GROUP BY with only one query

[fmisso (Fabrice)]

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

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.

[Vinayak_Sapre]

@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.

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