# Getting LAST record in aggr in ES|QL **URL:** \ **Category:** Elasticsearch\ **Tags:** esql\ **Created:** [January 7, 2025, 5:35pm UTC](https://discuss.elastic.co/t/getting-last-record-in-aggr-in-es-ql/372903 "2025-01-07T17:35:55Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![jfsardon](https://avatars.discourse-cdn.com/v4/letter/j/ac91a4/32.png) [@jfsardon](https://discuss.elastic.co/u/jfsardon)\ **Post date:** [January 7, 2025, 5:35pm UTC](https://discuss.elastic.co/t/getting-last-record-in-aggr-in-es-ql/372903/1 "2025-01-07T17:35:55Z")
Is there an simple way to get the last value of a field (@timestamp sorted) for each customer, hostname, etc.. For example : select logs-myindex-xxx | SORT (@timestamp) | STATS LAST (maxsize) BY customer, hostname... but where is no LAST or First stats function... any idea ? ---
**Author:** ![BenB196](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/benb196/32/83401_2.png) [@BenB196](https://discuss.elastic.co/u/BenB196)\ **Post date:** [January 7, 2025, 9:54pm UTC](https://discuss.elastic.co/t/getting-last-record-in-aggr-in-es-ql/372903/2 "2025-01-07T21:54:54Z")
This one got me for a bit as well. There is the [`TOP`](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-functions-operators.html#esql-top) function which should be able to achieve this. Set `limit` to `1` and change `order` to act as a `first/last` function. ---
**Author:** ![jfsardon](https://avatars.discourse-cdn.com/v4/letter/j/ac91a4/32.png) [@jfsardon](https://discuss.elastic.co/u/jfsardon)\ **Post date:** [January 8, 2025, 3:35pm UTC](https://discuss.elastic.co/t/getting-last-record-in-aggr-in-es-ql/372903/3 "2025-01-08T15:35:22Z")
Great ! It's not a very easy syntax, but it's works. Thanks Ben ! ---
**Author:** ![RainTown](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/raintown/32/140206_2.png) [@RainTown](https://discuss.elastic.co/u/RainTown)\ **Post date:** [January 8, 2025, 10:53pm UTC](https://discuss.elastic.co/t/getting-last-record-in-aggr-in-es-ql/372903/4 "2025-01-08T22:53:26Z")
Can you maybe share the ES\QL query you ended up with, so thread actually contains the solution, rather than (good) clues that lead to a solution. ---
**Author:** ![jfsardon](https://avatars.discourse-cdn.com/v4/letter/j/ac91a4/32.png) [@jfsardon](https://discuss.elastic.co/u/jfsardon)\ **Post date:** [January 9, 2025, 9:23am UTC](https://discuss.elastic.co/t/getting-last-record-in-aggr-in-es-ql/372903/5 "2025-01-09T09:23:55Z")
Sure ! Here's the request : `FROM logs-connectivity-qyyp | SORT @timestamp DESC | STATS lastState = TOP (stateStatus,1,"asc"), transitionTime=MAX(transitionTime) BY Client, hostname | WHERE lastState LIKE "Failed"` ---
**Author:** ![jfsardon](https://avatars.discourse-cdn.com/v4/letter/j/ac91a4/32.png) [@jfsardon](https://discuss.elastic.co/u/jfsardon)\ **Post date:** [January 9, 2025, 9:25am UTC](https://discuss.elastic.co/t/getting-last-record-in-aggr-in-es-ql/372903/6 "2025-01-09T09:25:47Z")
and I hope ES|QL will be enable in Canvas soon .... ---
**Author:** ![allatrue](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/allatrue/32/130067_2.png) [@allatrue](https://discuss.elastic.co/u/allatrue)\ **Post date:** [September 25, 2025, 6:49am UTC](https://discuss.elastic.co/t/getting-last-record-in-aggr-in-es-ql/372903/7 "2025-09-25T06:49:09Z")
Hi Jean, I’m wondering if this works correctly for your use case. Top function in this case returns the “smallest” value of “stateStatus” per bucket, not the last one. SORT command before STATS has no effect on the result. Is it the final version of your query? I have similar use-case and still looking for a solution. Regards Alexander ---
**Author:** ![ArielC](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/arielc/32/141229_2.png) [@ArielC](https://discuss.elastic.co/u/ArielC)\ **Post date:** [February 25, 2026, 7:56pm UTC](https://discuss.elastic.co/t/getting-last-record-in-aggr-in-es-ql/372903/8 "2026-02-25T19:56:45Z")
I am also in search of a way to get the most recent value, as opposed to the smaller or largest one. ---
**Author:** ![leandrojmp](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/leandrojmp/32/107231_2.png) [@leandrojmp](https://discuss.elastic.co/u/leandrojmp)\ **Post date:** [February 25, 2026, 10:39pm UTC](https://discuss.elastic.co/t/getting-last-record-in-aggr-in-es-ql/372903/9 "2026-02-25T22:39:45Z")
> [@ArielC](#): > > I am also in search of a way to get the most recent value, as opposed to the smaller or largest one. Have you tried using MAX()? Something like this: ```auto FROM index | STATS last_timestamp = MAX(event.ingested) ``` I use this on some queries to get the ingestion lag for some data streams: ```auto FROM index | STATS last_timestamp = MAX(event.ingested) | EVAL lag = DATE_DIFF("minute", last_timestamp, NOW()) | LIMIT 1 ``` ---
**Author:** ![ArielC](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/arielc/32/141229_2.png) [@ArielC](https://discuss.elastic.co/u/ArielC)\ **Post date:** [March 2, 2026, 5:30pm UTC](https://discuss.elastic.co/t/getting-last-record-in-aggr-in-es-ql/372903/10 "2026-03-02T17:30:07Z")
MAX works to get me the latest timestamp, but I need to extract the value of another field from the most recent document. I was able to actually get the latest value by using CONCAT to combine the value I am looking for with the timestamp. ```auto | EVAL timestamp_str = TO_STRING(@timestamp) // Create a string that looks like combines timestamp and size | EVAL combo = CONCAT(timestamp_str, " | ", TO_STRING(`file.size(mb)`)) | STATS latest_combo = MAX(combo) BY file.path, user.name, computer.name // Split the string back apart to get the size | EVAL latest_timestamp = TO_DATETIME(SUBSTRING(latest_combo, 1, 24)), latest_size = TO_DOUBLE(SUBSTRING(latest_combo, 28, 100)) ``` I don’t love this workaround, but it works. ---
**Author:** ![RainTown](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/raintown/32/140206_2.png) [@RainTown](https://discuss.elastic.co/u/RainTown)\ **Post date:** [March 2, 2026, 6:12pm UTC](https://discuss.elastic.co/t/getting-last-record-in-aggr-in-es-ql/372903/11 "2026-03-02T18:12:02Z")
Look at the [TOP](https://www.elastic.co/docs/reference/query-languages/esql/functions-operators/aggregation-functions/top) function, as enhanced in 9.3.0+ Sadly the outputField is a bit limited, allowing a keyword field as outputField would be a massive enhancement in many use cases IMHO, but it might be useful in your case? I asked about this [here](https://discuss.elastic.co/t/further-enhance-top-es-ql-function-please/384898) but sadly got no answer. ---
**Author:** ![mouhc1ne](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mouhc1ne/32/144573_2.png) [@mouhc1ne](https://discuss.elastic.co/u/mouhc1ne)\ **Post date:** [April 16, 2026, 6:33pm UTC](https://discuss.elastic.co/t/getting-last-record-in-aggr-in-es-ql/372903/12 "2026-04-16T18:33:40Z")
If on serverless, you should be able to use [LAST](https://www.elastic.co/docs/reference/query-languages/esql/functions-operators/aggregation-functions/last) already. If not, it's coming in the next 9.4 release. This also applies to [FIRST](https://www.elastic.co/docs/reference/query-languages/esql/functions-operators/aggregation-functions/first), [EARLIEST](https://www.elastic.co/docs/reference/query-languages/esql/functions-operators/aggregation-functions/earliest) and [LATEST](https://www.elastic.co/docs/reference/query-languages/esql/functions-operators/aggregation-functions/latest). In your example, you'd do one of these two equivalent options 1. `... | STATS LAST(@timestamp, your_other_field)` 2. `... | STATS EARLIEST(your_other_field)`. In this case, `@timestamp` is implied as the first parameter.