# ESQL beginner grouping question **URL:** \ **Category:** Elasticsearch\ **Tags:** esql\ **Created:** [August 14, 2025, 4:12pm UTC](https://discuss.elastic.co/t/esql-beginner-grouping-question/381054 "2025-08-14T16:12:56Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\ **Post date:** [August 14, 2025, 4:12pm UTC](https://discuss.elastic.co/t/esql-beginner-grouping-question/381054/1 "2025-08-14T16:12:56Z")
Hey, I am currently trying to convert a lengthy query DSL query with terms, top hits and max aggregations into a ESQL query, but I am failing at a basic requirement. Imagine the following three documents: ```auto PUT alr-test/_doc/1 { "@timestamp" : "2024-10-19T23:32:42.000Z", "day" : "2024-10-19T00:00:00.000Z", "minute_of_day" : 1412, "power" : 801 } PUT alr-test/_doc/2 { "@timestamp" : "2024-10-19T21:32:42.000Z", "day" : "2024-10-19T00:00:00.000Z", "minute_of_day" : 1292, "power" : 1400 } PUT alr-test/_doc/3 { "@timestamp" : "2023-06-24T20:35:47.000Z", "day" : "2023-06-24T00:00:00.000Z", "minute_of_day" : 1235, "power" : 18 } ``` What I would like to do: For each day, retrieve the latest `@timestamp` and `power` and `day` document. So for the 19th of October this would be `2024-10-19T23:32:42.000Z`/`801`/`2024-10-19T00:00:00.000Z` and for 24th of june there is only one doc. I played around with ```auto FROM alr-test | STATS MAX(minute_of_day), TOP(@timestamp, 1, "desc") BY day,power ``` But this does not work, the moment I add more than one `TOP` function or all the fields in the `BY` statement. I assume I am missing something absolutely trivial, that I don’t see after playing around. Thought about using `VALUES()` but that returns too much data. In SQL I would probably be joining with another CTA that only contains the latest timestamps for each day, which I cannot do here. Thanks for any pointers! –Alex ---
**Author:** ![Musab\_Dogan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/musab_dogan/32/70691_2.png) [@Musab\_Dogan](https://discuss.elastic.co/u/Musab_Dogan)\ **Post date:** [August 14, 2025, 9:09pm UTC](https://discuss.elastic.co/t/esql-beginner-grouping-question/381054/2 "2025-08-14T21:09:59Z")
> [@spinscale](#): > > In SQL I would probably be joining with another CTA that only contains the latest timestamps for each day, which I cannot do here. Hey Alex, Just to clarify you say `cannot do here` because you don’t want to create an additional index or something else? I’m asking because if you create an additional index with latest\_timestamp value the following query will work. ```auto FROM alr-test | LOOKUP join max_timestamps_index ON day | WHERE @timestamp == max_ts | DROP max_ts | SORT day ASC ``` ### ESQL-TOP vs DSL-top\_hits As far as I research because top\_hits and TOP works differently it’s not possible to convert this DSL query to ESQL. ESQL's TOP() function operates at the **field level** - it returns the top N values for each field independently. The top\_hits aggregation operates at the **document level** - it returns the top N complete documents that match your criteria. - **top\_hits** : Returns complete documents → preserves field relationships - **TOP()**: Returns individual field values → loses field relationships Regards, Musab ---
**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\ **Post date:** [August 15, 2025, 7:37am UTC](https://discuss.elastic.co/t/esql-beginner-grouping-question/381054/3 "2025-08-15T07:37:57Z")
Hey, yes, with `cannot do here` I was more referring to being too much work for a relatively small index with a couple hundred thousand items. Then it would probably make more sense to have a transform that creates the right data without any additional query. Your other remarks match with what I found, just way better written than I did - in the hopes that there is some functionality that I have been missing 🙂 Thanks for your answer! –Alex ---
**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\ **Post date:** [August 15, 2025, 1:54pm UTC](https://discuss.elastic.co/t/esql-beginner-grouping-question/381054/4 "2025-08-15T13:54:30Z")
Hey, how about this? ```auto FROM alr-test | STATS MAX(minute_of_day), VALUES(@timestamp), VALUES(power) BY day | RENAME `VALUES(@timestamp)` AS timestamps, `VALUES(power)` AS powers | EVAL first_ts = MV_FIRST(timestamps), first_power = MV_FIRST(powers) | KEEP first_ts, first_power ``` I am not sure of the order of the arrays is guaranteed, otherwise this would not work though. –Alex ---
**Author:** ![Musab\_Dogan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/musab_dogan/32/70691_2.png) [@Musab\_Dogan](https://discuss.elastic.co/u/Musab_Dogan)\ **Post date:** [August 15, 2025, 4:40pm UTC](https://discuss.elastic.co/t/esql-beginner-grouping-question/381054/5 "2025-08-15T16:40:11Z")
Hey, Thanks for the reply. It looks like the order is not guaranteed because it’s sorted by first indexed `_id`. Even changing the order and not changing the `_id` ended up with wrong results. So we will see the first indexed document `power` value on response regardless of anything 🙂 ![image](https://us1.discourse-cdn.com/elastic/original/3X/1/e/1e90e5341a2c16423fdcd462311c8271d4f6186a.jpeg) **Note:** Honestly, I’m surprised that ESQL doesn’t have this capability - or perhaps we just couldn’t find it. In `aggregations` `+` `top_hits` is one of the most frequently used queries, and many of my customers rely on it.