# ES|QL: How to count latest status per key without transform
**URL:** https://discuss.elastic.co/t/es-ql-how-to-count-latest-status-per-key-without-transform/384430
**Category:** Kibana
**Tags:** esql
**Created:** [January 8, 2026, 7:30am UTC](https://discuss.elastic.co/t/es-ql-how-to-count-latest-status-per-key-without-transform/384430 "2026-01-08T07:30:52Z")
**Posts on this page:** 2
**Page:** 1
### Author:  [@Tortoise](https://discuss.elastic.co/u/Tortoise)
#### Post date: [January 8, 2026, 7:30am UTC](https://discuss.elastic.co/t/es-ql-how-to-count-latest-status-per-key-without-transform/384430/1 "2026-01-08T07:30:53Z")
Hello Team,
I am trying to compute metrics based only on the latest record per key using ES|QL in 9.x version?
I have a time-series index where each entity (e.g. trainnumber) emits multiple events over time and I want to:
Consider only the latest record per trainnumber (by @timestamp)
Then compute counts based on the latest station value
Example :
trainnumber | @timestamp | station
------------+--------------------------+---------
T100 | 2026-01-05T10:00:00.000Z | START
T100 | 2026-01-05T10:05:00.000Z | MID
T100 | 2026-01-05T10:10:00.000Z | END
T200 | 2026-01-05T11:00:00.000Z | START
T200 | 2026-01-05T11:07:00.000Z | MID
T300 | 2026-01-05T12:00:00.000Z | START
Latest Status :
T100 → END
T200 → MID
T300 → START
Expected Metric :
So START = 1 , MID = 1 , END = 1
I know one way is creating a transform index which will keep the latest record & on that index we can easily have the Metric for the station.
Wanted to understand if there is any other way to avoid creating transform & new index.
---
### Author:  [@RainTown](https://discuss.elastic.co/u/RainTown)
#### Post date: [January 8, 2026, 8:20am UTC](https://discuss.elastic.co/t/es-ql-how-to-count-latest-status-per-key-without-transform/384430/2 "2026-01-08T08:20:14Z")
In the couple of years I've been active on the forum, this type of Q has come up pretty often, and it's always been a bit awkward to answer. The TOP function, which will be enhanced soon with an OutputField, has close to the functionality you need.
>
>
> There are cases where we would like to answer queries like:
> "give me list of em…ployees sorted by their salaries"
> "give me list of countries sorted by their area"
> etc.
> Currently, the \`TOP\` aggregation function only supports one field which is used as both sort field and the output field.
> This PR enhances the \`TOP\` function by adding an optional parameter \`mapToField\` which is the field we want as the output. The sorting will still be performed on the \`field\`, just like today.
>
> Example:
> "give me salaries of 3 youngest employees by gender"
> \`\`\`
> FROM employees
> | STATS youngest\_employees = TOP(birth\_date, 3, "desc"),
> youngest\_employees\_salaries = TOP(birth\_date, 3, "desc", salary)
> BY gender
> | SORT gender
> | KEEP gender, youngest\_employees, youngest\_employees\_salaries
> ;
>
> gender:keyword | youngest\_employees:datetime | youngest\_employees\_salaries:integer
> F | \[1964-10-18T00:00:00.000Z, 1964-06-02T00:00:00.000Z, 1963-03-21T00:00:00.000Z\] | \[25976, 56371, 43602\]
> M | \[1965-01-03T00:00:00.000Z, 1964-06-11T00:00:00.000Z, 1964-04-18T00:00:00.000Z\] | \[37702, 45656, 46595\]
> null | \[1963-06-07T00:00:00.000Z, 1963-06-01T00:00:00.000Z, 1961-05-02T00:00:00.000Z\] | \[48735, 45797, 61358\]
> ;
> \`\`\`
>
> Fixes https://github.com/elastic/elasticsearch/issues/128630
What's seems to be missing, and will still be missing, is support for an outputField of keyword type. If your train number was actually a number then I think TOP that might be part of 9.3 will do the job.
[https://www.elastic.co/docs/reference/query-languages/esql/functions-operators/aggregation-functions#esql-top](https://www.elastic.co/docs/reference/query-languages/esql/functions-operators/aggregation-functions#esql-top)
EDIT: maybe actually better if the _station_ were a number. With latest SNAPSHOT (\*) I see:
```auto
FROM trains | STATS station=TOP(@timestamp, 1,"DESC",stationnumber) BY trainnumber | STATS train_count=COUNT(station) BY station | SORT train_count ASC
```
seems to do the job, given this data loaded into trains index
```auto
trainnumber,@timestamp,stationnumber
T500,2026-01-05T09:00:00.000Z,1
T100,2026-01-05T10:00:00.000Z,1
T100,2026-01-05T10:05:00.000Z,2
T400,2026-01-05T10:05:00.000Z,1
T100,2026-01-05T10:10:00.000Z,3
T200,2026-01-05T11:00:00.000Z,1
T200,2026-01-05T11:07:00.000Z,2
T300,2026-01-05T12:00:00.000Z,1
T400,2026-01-05T12:05:00.000Z,2
T400,2026-01-05T12:10:00.000Z,3
T500,2026-01-05T12:15:00.000Z,2
```
implcitly 1==START, 2==MID, 3==END
That all said, these types of common questions could be wayyy simpler to answer in ES|QL if the TOP function could be extended further with keyword fields as OutputField.
(\*) snapshots are not released. versions!