# Is it possible to return only the most recent of 'each document'? **URL:** \ **Category:** Elasticsearch\ **Created:** [March 12, 2019, 5:18pm UTC](https://discuss.elastic.co/t/is-it-possible-to-return-only-the-most-recent-of-each-document/172011 "2019-03-12T17:18:00Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![Daniel\_Webb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/daniel_webb/32/41834_2.png) [@Daniel\_Webb](https://discuss.elastic.co/u/Daniel_Webb)\ **Post date:** [March 12, 2019, 5:18pm UTC](https://discuss.elastic.co/t/is-it-possible-to-return-only-the-most-recent-of-each-document/172011/1 "2019-03-12T17:18:01Z")
Hi Elasticsearch community, total novice here with my first post. Is it possible to design a query that will return only the most recent of each document in an index? I'd like to store snapshots of our projects as they change, and then be able to retrieve the nearest-oldest snapshot for each project at any given time. For example if I stored a snapshot of project 1 and 2 on 2019-03-10, then another snapshot of project 1 on 2019-03-14, my index would have: ``` { project_id: 1, hours_remaining: 10, timestamp: 2019-03-10 } { project_id: 2, hours_remaining: 20, timestamp: 2019-03-10 } { project_id: 1; hours_remaining: 5; timestamp: 2019-03-14 } ``` Then if I queried with a timestamp filter of 2019-03-15 I'd get the last two records, but if I used a timestamp filter of 2019-03-13 I'd get the first two. Is this possible? I've read about and discounted versioning, which this is sort of similar too. I've read about time series and event series as well, but I'm not sure if this counts as that kind of data Any advice greatly appreciated. I'm familiar with SQL, but totally new to the Elastic world. Regards Daniel ---
**Author:** ![gbrown](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/gbrown/32/34482_2.png) [@gbrown](https://discuss.elastic.co/u/gbrown)\ **Post date:** [March 12, 2019, 11:26pm UTC](https://discuss.elastic.co/t/is-it-possible-to-return-only-the-most-recent-of-each-document/172011/2 "2019-03-12T23:26:12Z")
Hi Daniel, thanks for your interest in Elasticsearch! If you just want one of the projects at a time, this is very straightforward - you just make a query sorted by the date: ```auto GET testindex/_search { "query": { "match": { "project_id": "1" } }, "sort": [ { "timestamp": { "order": "desc" } } ], "size": 1 } ``` If you want to retrieve the most recent document for all projects in one query, you can use [Field Collapsing](https://www.elastic.co/guide/en/elasticsearch/reference/6.6/search-request-collapse.html) like so: ```auto GET testindex/_search { "size": 10, "query": { "match_all": {} }, "collapse": { "field": "project_id", "inner_hits": { "name": "most_recent", "size": 1, "sort": [{"timestamp": "desc"}] } } } ``` You'll have to adjust the size parameter based on the number of projects you have, of course, and the response is a bit verbose: ```auto { //some fields elided for readability "hits" : { "total" : { "value" : 3, "relation" : "eq" }, "max_score" : null, "hits" : [ { "_index" : "testindex", "_type" : "_doc", "_id" : "MP0idGkBCYiTQNOubpiI", "_score" : 1.0, "_source" : { "project_id" : "1", "hours_remaining" : 10, "timestamp" : "2019-03-10" }, "fields" : { "project_id" : [ "1" ] }, "inner_hits" : { "most_recent" : { "hits" : { "total" : { "value" : 2, "relation" : "eq" }, "max_score" : null, "hits" : [ { "_index" : "testindex", "_type" : "_doc", "_id" : "Mv0idGkBCYiTQNOubpiI", "_score" : null, "_source" : { "project_id" : "1", "hours_remaining" : 5, "timestamp" : "2019-03-14" }, "sort" : [ 1552521600000 ] } ] } } } }, { "_index" : "testindex", "_type" : "_doc", "_id" : "Mf0idGkBCYiTQNOubpiI", "_score" : 1.0, "_source" : { "project_id" : "2", "hours_remaining" : 20, "timestamp" : "2019-03-10" }, "fields" : { "project_id" : [ "2" ] }, "inner_hits" : { "most_recent" : { "hits" : { "total" : { "value" : 1, "relation" : "eq" }, "max_score" : null, "hits" : [ { "_index" : "testindex", "_type" : "_doc", "_id" : "Mf0idGkBCYiTQNOubpiI", "_score" : null, "_source" : { "project_id" : "2", "hours_remaining" : 20, "timestamp" : "2019-03-10" }, "sort" : [ 1552176000000 ] } ] } } } } ] } } ``` The thing to pay attention to is the `inner_hits` field of each hit - the `_source` directly inside each hit is just the first document encountered for each `project_id`. So for example, for the first result, you'd look at the value of `.hits.hits[0].inner_hits.most_recent.hits.hits[0]._source`, for the second, `.hits.hits[1].inner_hits.most_recent.hits.hits[0]._source`, and so on. Does that help get you what you need? It's also worth noting that Elasticsearch does support [a limited subset of SQL](https://www.elastic.co/guide/en/elasticsearch/reference/6.6/xpack-sql.html), although I don't think that it would be helpful in this case as it doesn't support the necessary GROUP BY functions (yet), but it may be helpful as you explore Elasticsearch. ---
**Author:** ![Daniel\_Webb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/daniel_webb/32/41834_2.png) [@Daniel\_Webb](https://discuss.elastic.co/u/Daniel_Webb)\ **Post date:** [March 13, 2019, 9:27am UTC](https://discuss.elastic.co/t/is-it-possible-to-return-only-the-most-recent-of-each-document/172011/3 "2019-03-13T09:27:22Z")
Hi Gordon, Thank you for the response, appreciate your effort. It is the second scenario that we're interested in -retrieving the most recent document for all projects. I've not come across collapsing before, I'll spend some time getting my head around it to see if it will do the trick. At least I know now that we're not missing an obvious solution! Thanks ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [April 10, 2019, 9:27am UTC](https://discuss.elastic.co/t/is-it-possible-to-return-only-the-most-recent-of-each-document/172011/4 "2019-04-10T09:27:26Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.