# GROUP BY with only one query

**URL:** <https://discuss.elastic.co/t/group-by-with-only-one-query/236411>\
**Category:** Elasticsearch\
**Created:** [June 9, 2020, 7:42pm UTC](https://discuss.elastic.co/t/group-by-with-only-one-query/236411 "2020-06-09T19:42:42Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![fmisso](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/fmisso/32/50267_2.png) [@fmisso](https://discuss.elastic.co/u/fmisso)\
**Post date:** [June 9, 2020, 7:42pm UTC](https://discuss.elastic.co/t/group-by-with-only-one-query/236411/1 "2020-06-09T19:42:42Z")

</div>

Hello,  
I'm looking for a solution to do this :

```auto
SELECT t.nom, t.latitude, l.longitude, r.MaxTime
FROM (
      SELECT name, MAX(time) as MaxTime
      FROM BusTable
      GROUP BY name ) r
INNER JOIN BusTable t
ON t.name = r.name AND t.time = r.MaxTime

```

Is it possible to do it with only one elasticsearch query (with agregates) ?  
Thank you for you response  
F.M.

---

<div class="post-metadata">

**Author:** ![Vinayak\_Sapre](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vinayak_sapre/32/45939_2.png) [@Vinayak\_Sapre](https://discuss.elastic.co/u/Vinayak_Sapre)\
**Post date:** [June 27, 2020, 4:38am UTC](https://discuss.elastic.co/t/group-by-with-only-one-query/236411/2 "2020-06-27T04:38:14Z")

</div>

@fmisso  
I top\_hits can take you close to what you are looking for. Especially if there is only one entry for MaxTime for each name, you can use following query. You can adjust size inside top\_hits to get more matches per name and do client side filtering.

````auto
  "size": 0,
  "aggs": {
    "by_name": {
      "terms": {
        "field": "name"
      },
      "aggs": {
        "top_hits_matches": {
          "top_hits": {
            "sort": {
              "time": {
                "order": "desc"
              }
            },
            "size": 1
          }
        }
      }
    }
  }
}```
````

---

<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 25, 2020, 4:38am UTC](https://discuss.elastic.co/t/group-by-with-only-one-query/236411/3 "2020-07-25T04:38:22Z")

</div>

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