# GET latest value for each unique value?

**URL:** <https://discuss.elastic.co/t/get-latest-value-for-each-unique-value/123196>\
**Category:** Elasticsearch\
**Created:** [March 9, 2018, 5:53am UTC](https://discuss.elastic.co/t/get-latest-value-for-each-unique-value/123196 "2018-03-09T05:53:20Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Sjaak01](https://avatars.discourse-cdn.com/v4/letter/s/73ab20/32.png) [@Sjaak01](https://discuss.elastic.co/u/Sjaak01)\
**Post date:** [March 9, 2018, 5:53am UTC](https://discuss.elastic.co/t/get-latest-value-for-each-unique-value/123196/1 "2018-03-09T05:53:20Z")

</div>

Hi,

I have the query below which will provide me with the latest error for a certain location. However I have multiple locations and I would like to pull the latest error for each location (without having to put in a whole list of locations) with only one query. What would the best way to do that?

```auto
{
"query": {
    "bool": {
      "must": [
        {
                "exists": {
                    "field": "error"
                }
        },
        {
          "term": {
            "location": "locationA" 
          }
        }
      ]
    }
  },
    "size": 1,
  "sort": [
    {
      "@timestamp": {
        "order": "desc"
      }
    }
  ]
}
```

---

<div class="post-metadata">

**Author:** ![Shaoranlaos](https://avatars.discourse-cdn.com/v4/letter/s/c57346/32.png) [@Shaoranlaos](https://discuss.elastic.co/u/Shaoranlaos)\
**Post date:** [March 9, 2018, 6:41am UTC](https://discuss.elastic.co/t/get-latest-value-for-each-unique-value/123196/2 "2018-03-09T06:41:03Z")

</div>

I think you will need an aggregation for this.  
Which es version are you using?

Sinc es 5.5? there is a Top Hit aggregation that i think does what you have in mind.  
[https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-top-hits-aggregation.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-top-hits-aggregation.html)

---

<div class="post-metadata">

**Author:** ![Sjaak01](https://avatars.discourse-cdn.com/v4/letter/s/73ab20/32.png) [@Sjaak01](https://discuss.elastic.co/u/Sjaak01)\
**Post date:** [March 9, 2018, 7:24am UTC](https://discuss.elastic.co/t/get-latest-value-for-each-unique-value/123196/3 "2018-03-09T07:24:26Z")

</div>

Doesn't look very easy to understand...

I'm on 6.2.

---

<div class="post-metadata">

**Author:** ![Shaoranlaos](https://avatars.discourse-cdn.com/v4/letter/s/c57346/32.png) [@Shaoranlaos](https://discuss.elastic.co/u/Shaoranlaos)\
**Post date:** [March 9, 2018, 7:58am UTC](https://discuss.elastic.co/t/get-latest-value-for-each-unique-value/123196/4 "2018-03-09T07:58:19Z")

</div>

here is a query which hopefully works for your example  
under the key "aggregations" is the grouped view and not under "hits"

```auto
{
  "size": 0,
  "aggs": {
    "2": {
      "terms": {
        "field": "location",
        "size": 150,
        "order": {
          "_term": "asc"
        }
      },
      "aggs": {
        "1": {
          "top_hits": {
            "docvalue_fields": [
              "error"
            ],
            "_source": "error",
            "size": 1,
            "sort": [
              {
                "@timestamp": {
                  "order": "desc"
                }
              }
            ]
          }
        }
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![Sjaak01](https://avatars.discourse-cdn.com/v4/letter/s/73ab20/32.png) [@Sjaak01](https://discuss.elastic.co/u/Sjaak01)\
**Post date:** [March 9, 2018, 8:18am UTC](https://discuss.elastic.co/t/get-latest-value-for-each-unique-value/123196/5 "2018-03-09T08:18:10Z")

</div>

Thanks. That example is a lot easier to read. I will get to work with it.

Just to make sure I understand what is going on:

`"size": 150,`

Is the maximum number of results? E.g. if I had more than 150 locations I would need to change it to whatever amount of locations I have?

`"_term": "asc"`

Orders the results alphabetically?

`"_source": "error"`

The agg sets the value as \_source?

---

<div class="post-metadata">

**Author:** ![Shaoranlaos](https://avatars.discourse-cdn.com/v4/letter/s/c57346/32.png) [@Shaoranlaos](https://discuss.elastic.co/u/Shaoranlaos)\
**Post date:** [March 9, 2018, 8:42am UTC](https://discuss.elastic.co/t/get-latest-value-for-each-unique-value/123196/6 "2018-03-09T08:42:59Z")

</div>

> [@Sjaak01](#):
>
> "size": 150,
> 
> Is the maximum number of results? E.g. if I had more than 150 locations I would need to change it to whatever amount of locations I have?

yes that is correct

> [@Sjaak01](#):
>
> "\_term": "asc"
> 
> Orders the results alphabetically?

also correct

> [@Sjaak01](#):
>
> "\_source": "error"
> 
> The agg sets the value as \_source?

Answers: Which fields of the document with the highest timestamp should be in the response?  
under "\_source" are defined the fields which should be shown in the hits \_source field  
and under "docvalue\_fields" are defined the fields which should be shown under a "fields" field in the hit

---

<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:** [April 6, 2018, 8:43am UTC](https://discuss.elastic.co/t/get-latest-value-for-each-unique-value/123196/7 "2018-04-06T08:43:10Z")

</div>

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