# How to aggregate by the max agg result?

**URL:** <https://discuss.elastic.co/t/how-to-aggregate-by-the-max-agg-result/56991>\
**Category:** Elasticsearch\
**Created:** [August 2, 2016, 10:24am UTC](https://discuss.elastic.co/t/how-to-aggregate-by-the-max-agg-result/56991 "2016-08-02T10:24:46Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![goofansu](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/goofansu/32/4755_2.png) [@goofansu](https://discuss.elastic.co/u/goofansu)\
**Post date:** [August 2, 2016, 10:24am UTC](https://discuss.elastic.co/t/how-to-aggregate-by-the-max-agg-result/56991/1 "2016-08-02T10:24:46Z")

</div>

I save the player levelup logs in es like following:

```
{uid: 1, lv: 1, time: 1470119008, type: "leveup"}
{uid: 1, lv: 2, time: 1470120008, type: "leveup"}
{uid: 2, lv: 1, time: 1470119108, type: "leveup"}
{uid: 2, lv: 2, time: 1470120008, type: "leveup"}
{uid: 2, lv: 3, time: 1470121008, type: "leveup"}
...

```

Given a range of time, I want to know the lv distribution. For example:

in 1470119008 - 1470120008, the results will be:

```
{lv: 1, player_count: 2}
{lv: 2, player_count: 2}

```

My query:

```
GET player_logs/_search
{
  "size": 0, 
  "query": {
    "match": {
      "type": "levelup"
    }
  },
  "aggs": {
    "group_by_uid": {
      "terms": {
        "field": "uid"
      },
      "aggs": {
        "max_lv": {
          "max": {
            "field": "lv"
          }
        }
      }
    }
  }
}

```

Now I should aggregate by above results to generate the final results, in sql, it should be `select a.lv, count(a.*) from (select uid, max(lv) from players_log where type='levelup' group by uid) a`.

But I don't know how to do that in es.

Please help, thanks

---

<div class="post-metadata">

**Author:** ![nik9000](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nik9000/32/44947_2.png) [@nik9000](https://discuss.elastic.co/u/nik9000)\
**Post date:** [August 3, 2016, 12:41pm UTC](https://discuss.elastic.co/t/how-to-aggregate-by-the-max-agg-result/56991/2 "2016-08-03T12:41:35Z")

</div>

Pipeline aggregations should be able to do it but won't be that efficient because they have to pull the results back to the coordinating node rather than run near the data.

Keep in mind that a terms aggregation can make a lot of buckets too - in your case one per player. If you have many players that isn't going to be efficient or quick. It might be enough buckets to cause the query to fail outright. So this question is a fairly inefficient one to answer.

---

<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:** [July 5, 2017, 10:30pm UTC](https://discuss.elastic.co/t/how-to-aggregate-by-the-max-agg-result/56991/3 "2017-07-05T22:30:27Z")

</div>