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 ?

This one got me for a bit as well. There is the TOP function which should be able to achieve this. Set limit to 1 and change order to act as a first/last function.

Great ! It's not a very easy syntax, but it's works. Thanks Ben !

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.

and I hope ES|QL will be enable in Canvas soon ....

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 l…

I am also in search of a way to get the most recent value, as opposed to the smaller or largest one.

[image] 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: FROM index | STATS last_timestamp = MAX(event.ingested) I use this on some queries to get the ingestion lag for some data st…

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. | EVAL timestamp_str = TO_STRING(@timestamp) // Create a …

Look at the 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 but sadly got no answer.

If on serverless, you should be able to use LAST already. If not, it's coming in the next 9.4 release. This also applies to FIRST , EARLIEST and LATEST . In your example, you'd do one of these two equivalent options ... | STATS LAST(@timestamp, your_other_field) ... | STATS EARLIEST(your_other_fiel…

Getting LAST record in aggr in ES|QL

Elastic Stack Elasticsearch

jfsardon (Jean Francois Sardon Arraz) January 9, 2025, 9:23am 5

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"

Double Terms aggregation with top_hits sub-aggregation not working

Topic		Replies	Views
ESQL Get a specific field in the most recent document from a group of documents Elasticsearch esql	0	229	April 16, 2025
ES\|QL: How to count latest status per key without transform Kibana esql	1	55	January 8, 2026
Get last document string value in each bucket Elasticsearch	2	1878	January 30, 2021
How to query for "max" value? Elasticsearch	4	542	July 5, 2017
Get newest value per distinct field Elasticsearch	3	1138	August 12, 2020

Getting LAST record in aggr in ES|QL

Related topics