# Elasticsearch query to return the most recent of 'each document' based on a condition

**URL:** <https://discuss.elastic.co/t/elasticsearch-query-to-return-the-most-recent-of-each-document-based-on-a-condition/220927>\
**Category:** Elasticsearch\
**Created:** [February 25, 2020, 9:38pm UTC](https://discuss.elastic.co/t/elasticsearch-query-to-return-the-most-recent-of-each-document-based-on-a-condition/220927 "2020-02-25T21:38:18Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![dagus2020](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dagus2020/32/63359_2.png) [@dagus2020](https://discuss.elastic.co/u/dagus2020)\
**Post date:** [February 25, 2020, 9:38pm UTC](https://discuss.elastic.co/t/elasticsearch-query-to-return-the-most-recent-of-each-document-based-on-a-condition/220927/1 "2020-02-25T21:38:18Z")

</div>

0

I am trying to retrieve the most recent version of each document in my dataset when the document is not already archived ( `archived: false` ). So when any version of the document has archived set to true, it should not appear in my result.

An example of my dataset:

```auto
 {
    name: "soccer game",
    base_id: 1,
    hours_remaining: 10,
    updatedDate: 2019 - 03 - 10,
    archived: false
}

{
    name: "basketball game",
    base_id: 2,
    hours_remaining: 20,
    updatedDate: 2019 - 03 - 10,
    archived: false
}

{
    name: "soccer game",
    base_id: 1,
    hours_remaining: 5,
    updatedDate: 2019 - 03 - 14,
    archived: true
}

```

The expected result is :

```auto
{
    name: 'basketball game",
    base_id: 2,
    hours_remaining: 20,
    timestamp: 2019 - 03 - 10,
    archived: false
}

```

After writing several queries, I haven't been able to achieve my goal. This is one of my attempts.

```auto
{
  "size": 10, 
   "query":{"bool":{"must":[{"query_string":{"query":"*","fields":["name.keyword"]}},{"term":{"archived":false}}]}},
   "collapse": {
    "field": "base_id",
    "inner_hits": {
      "name": "most_recent",
      "size": 1,
      "sort": [{"updatedDate": "desc"}]
    }
  }
}

```

What am I doing wrong?

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [March 24, 2020, 9:38pm UTC](https://discuss.elastic.co/t/elasticsearch-query-to-return-the-most-recent-of-each-document-based-on-a-condition/220927/2 "2020-03-24T21:38:29Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.