# How to get last X documents sorted by a timestamp **URL:** \ **Category:** Elasticsearch\ **Created:** [July 17, 2019, 3:35pm UTC](https://discuss.elastic.co/t/how-to-get-last-x-documents-sorted-by-a-timestamp/191046 "2019-07-17T15:35:36Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![jimmyn](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jimmyn/32/50392_2.png) [@jimmyn](https://discuss.elastic.co/u/jimmyn)\ **Post date:** [July 17, 2019, 3:35pm UTC](https://discuss.elastic.co/t/how-to-get-last-x-documents-sorted-by-a-timestamp/191046/1 "2019-07-17T15:35:36Z")
I have a huge set of email events of different types e.g. `sent`, `delivered`, `complaint`, `bounced` etc. My goal is: - get the last 10k `sent` events sorted by a timestamp - find the timestamp of the earliest event in this subset - use [terms aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html#_filtering_values_with_partitions) to get the count of all event types starting from that time till now I'm having troubles with the first bullet. Is there any way to achieve that with elastic search? UP ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [July 17, 2019, 3:36pm UTC](https://discuss.elastic.co/t/how-to-get-last-x-documents-sorted-by-a-timestamp/191046/2 "2019-07-17T15:36:28Z")
> [@jimmyn](#): > > Is there any way to achieve that with elasticsearch? Yes. > [@jimmyn](#): > > I'm having troubles with the first bullet. What did you try so far? ---
**Author:** ![jimmyn](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jimmyn/32/50392_2.png) [@jimmyn](https://discuss.elastic.co/u/jimmyn)\ **Post date:** [July 17, 2019, 3:41pm UTC](https://discuss.elastic.co/t/how-to-get-last-x-documents-sorted-by-a-timestamp/191046/3 "2019-07-17T15:41:15Z")
I've tired [top hits](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-top-hits-aggregation.html) aggregation but the size I need is too large, I can set the `size` param on the query itself but then I'll get 10k results in the output. What I need, seems to be, an aggregation that will allow me to get only 10k first results and then another nested aggregation that will get only the first one of that set. Ideally I would like to achieve all 3 steps in one query but if I can get an event timestamp in one query I know how to do the last aggregation in a separate query. ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [July 17, 2019, 3:42pm UTC](https://discuss.elastic.co/t/how-to-get-last-x-documents-sorted-by-a-timestamp/191046/4 "2019-07-17T15:42:09Z")
Why not just a normal search without aggs? ---
**Author:** ![jimmyn](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jimmyn/32/50392_2.png) [@jimmyn](https://discuss.elastic.co/u/jimmyn)\ **Post date:** [July 17, 2019, 3:44pm UTC](https://discuss.elastic.co/t/how-to-get-last-x-documents-sorted-by-a-timestamp/191046/5 "2019-07-17T15:44:34Z")
How can I instruct es to get 10k sorted events of specific type and then return only the first (or last) one? ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [July 17, 2019, 3:45pm UTC](https://discuss.elastic.co/t/how-to-get-last-x-documents-sorted-by-a-timestamp/191046/6 "2019-07-17T15:45:53Z")
filter by type of event. sort on the time stamp and set size to 1? But I might be missing the problem. Could you provide a full recreation script as described in [About the Elasticsearch category](https://discuss.elastic.co/t/about-the-elasticsearch-category/21). It will help to better understand what you are doing. Please, try to keep the example as simple as possible. ---
**Author:** ![jimmyn](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jimmyn/32/50392_2.png) [@jimmyn](https://discuss.elastic.co/u/jimmyn)\ **Post date:** [July 17, 2019, 4:06pm UTC](https://discuss.elastic.co/t/how-to-get-last-x-documents-sorted-by-a-timestamp/191046/7 "2019-07-17T16:06:55Z")
I can refrase: I have a sequence of events in desc order by timestamp and I want to know the timestamp of the event with index == 10k. If I reverse the order and get the first document, it will be the first event ever, but I need the event that happened 10k events ago. [first event]------- a lot of events -------[event that I need]------- 10k events -------[last event] ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [July 17, 2019, 4:33pm UTC](https://discuss.elastic.co/t/how-to-get-last-x-documents-sorted-by-a-timestamp/191046/8 "2019-07-17T16:33:02Z")
Might traversing the results from back to front be an option? ---
**Author:** ![jimmyn](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jimmyn/32/50392_2.png) [@jimmyn](https://discuss.elastic.co/u/jimmyn)\ **Post date:** [July 17, 2019, 4:35pm UTC](https://discuss.elastic.co/t/how-to-get-last-x-documents-sorted-by-a-timestamp/191046/9 "2019-07-17T16:35:13Z")
What do you mean? ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [July 17, 2019, 4:36pm UTC](https://discuss.elastic.co/t/how-to-get-last-x-documents-sorted-by-a-timestamp/191046/10 "2019-07-17T16:36:12Z")
Basically do the final sort at the client. I do not think there is a way to do it in the original request. ---
**Author:** ![jimmyn](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jimmyn/32/50392_2.png) [@jimmyn](https://discuss.elastic.co/u/jimmyn)\ **Post date:** [July 17, 2019, 5:59pm UTC](https://discuss.elastic.co/t/how-to-get-last-x-documents-sorted-by-a-timestamp/191046/12 "2019-07-17T17:59:45Z")
Any ideas? I would really appreciate your help ---
**Author:** ![jimmyn](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jimmyn/32/50392_2.png) [@jimmyn](https://discuss.elastic.co/u/jimmyn)\ **Post date:** [July 18, 2019, 6:04am UTC](https://discuss.elastic.co/t/how-to-get-last-x-documents-sorted-by-a-timestamp/191046/13 "2019-07-18T06:04:22Z")
We are talking about 10k results. Maybe I can use scripts somehow to increment a variable in search and cut off when it equals to 10k? ---
**Author:** ![jimmyn](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jimmyn/32/50392_2.png) [@jimmyn](https://discuss.elastic.co/u/jimmyn)\ **Post date:** [July 20, 2019, 8:26am UTC](https://discuss.elastic.co/t/how-to-get-last-x-documents-sorted-by-a-timestamp/191046/14 "2019-07-20T08:26:47Z")
I think I've found the solution to my problem. Elasticsearch default result window is 10k, it means that I can build a query like this: ```auto GET events/_search { "from": 9999, "size": 1, "sort": [ { "event_timestamp": { "order": "desc" } } ], "query": { "bool": { "must": [ { "match": { "event_type": "_email.send" } } ] } } } ``` It should sort all the events by `event_timestamp`, set the page size to 1 and get a 9999th page, so I suppose it should return the `_email.send` event that happened 9999 events ago. @dadoonet Is it going to work as I think it should? ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [August 17, 2019, 8:26am UTC](https://discuss.elastic.co/t/how-to-get-last-x-documents-sorted-by-a-timestamp/191046/15 "2019-08-17T08:26:47Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.