# Max() aggregation with group by and then return all fields **URL:** \ **Category:** Elasticsearch\ **Created:** [March 18, 2019, 7:52am UTC](https://discuss.elastic.co/t/max-aggregation-with-group-by-and-then-return-all-fields/172719 "2019-03-18T07:52:01Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![hopeng](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hopeng/32/42195_2.png) [@hopeng](https://discuss.elastic.co/u/hopeng)\ **Post date:** [March 18, 2019, 7:52am UTC](https://discuss.elastic.co/t/max-aggregation-with-group-by-and-then-return-all-fields/172719/1 "2019-03-18T07:52:01Z")
Hi, I have a collection of articles with different author, title and revisions. Can I run a search to get all the articles with the biggest revision in their author+title group (records in bold)? Author | PublishedDate | Revision | Title ---------------+------------------------+---------------+--------------- James |2019-02-04T00:00:00.000Z|1 |I wonder why **James |2019-03-04T00:00:00.000Z|2 |I wonder why** **Parker |2019-03-04T00:00:00.000Z|1 |The Endgame** I tried terms+max aggregation with top\_hits. The returned top hits record is not really with the max revision (=2): ``` { "aggs" : { "groupByAuthor" : { "terms" : { "field" : "Author.keyword" }, "aggs": { "groupByTitle": { "terms" : { "field" : "Title.keyword" }, "aggs": { "maxRevision": { "max": { "field": "Revision" } }, "top_trades_hits": { "top_hits": { "size" : 1 } } } } } } } } ``` I'm new to elasticsearch. Sorry if this question is already asked somewhere else, but I did some research and couldn't find the answer. Thank you. ---
**Author:** ![mjunaidmuzammil](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mjunaidmuzammil/32/56910_2.png) [@mjunaidmuzammil](https://discuss.elastic.co/u/mjunaidmuzammil)\ **Post date:** [March 18, 2019, 8:37am UTC](https://discuss.elastic.co/t/max-aggregation-with-group-by-and-then-return-all-fields/172719/2 "2019-03-18T08:37:38Z")
Can you share the response that you are getting? Are you looking at the aggregations key in response? ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [March 18, 2019, 8:47am UTC](https://discuss.elastic.co/t/max-aggregation-with-group-by-and-then-return-all-fields/172719/3 "2019-03-18T08:47:05Z")
Hi James. > [@hopeng](#): > > I tried terms+max aggregation with top\_hits. The `max` and `top_hits` are two independent summaries - the max calculation has no influence on the top hits. You need to use the `sort` feature in the top\_hits aggregation to get the highest revision. ---
**Author:** ![hopeng](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hopeng/32/42195_2.png) [@hopeng](https://discuss.elastic.co/u/hopeng)\ **Post date:** [March 18, 2019, 10:42am UTC](https://discuss.elastic.co/t/max-aggregation-with-group-by-and-then-return-all-fields/172719/5 "2019-03-18T10:42:43Z")
Hi Mark, Thank you 'sort' with top\_hits solves the issue if there's only one result per group. I have another problem that each group actually have multiple matching records (in bold) like so: Title | Author | PublishedDate | Revision | Reviewer | ReviewComment ---------------+---------------+------------------------+---------------+---------------+----------------- The Endgame |Parker |2018-12-04|1 |Martin |not good **The Endgame |Parker |2018-12-14|2 |Henry |passed** I wonder why |James |2019-02-04|1 |Mary |needs improvement **I wonder why |James |2019-03-04|2 |Jack |awesome!** **I wonder why |James |2019-03-04|2 |Jones |not bad** I need to get all records with max Revision in each group. So I cannot set a fixed 'size' for top\_hits. What should I do? ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [March 18, 2019, 10:49am UTC](https://discuss.elastic.co/t/max-aggregation-with-group-by-and-then-return-all-fields/172719/6 "2019-03-18T10:49:08Z")
> [@hopeng](#): > > I need to get all records with max Revision in each group. Sounds like you're wanting another level of `terms` aggregation underneath title which is a grouping for the `reviewer`. So you should have a hierarchy of ``` terms - author terms - title terms - reviewer top_hits - size 1, sort by date descending ``` If this ends up being a lot of data for one request you might want to consider using the `composite` aggregation and using the `after` param to break it into multiple requests. ---
**Author:** ![hopeng](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hopeng/32/42195_2.png) [@hopeng](https://discuss.elastic.co/u/hopeng)\ **Post date:** [March 18, 2019, 11:33am UTC](https://discuss.elastic.co/t/max-aggregation-with-group-by-and-then-return-all-fields/172719/7 "2019-03-18T11:33:37Z")
hmm Adding “terms - reviewers” returns records with Revision=1 which is not what I need. If it’s SQL it will look like: select \* from table group by Auhor, Title having Revision=max(Revision) I’m keen to know how composite aggregation and “after” will solve the problem. It doesn’t seem straightforward. ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [March 18, 2019, 11:45am UTC](https://discuss.elastic.co/t/max-aggregation-with-group-by-and-then-return-all-fields/172719/8 "2019-03-18T11:45:09Z")
> [@hopeng](#): > > Hmm Adding “terms - reviewers” returns records with Revision=1 which is not what I need. Are you sorting in the right direction? Is revision=1 the highest recorded revision for a given author/title/reviewer or are you saying a revision=2 record is missing for one of these combos? ---
**Author:** ![hopeng](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hopeng/32/42195_2.png) [@hopeng](https://discuss.elastic.co/u/hopeng)\ **Post date:** [March 18, 2019, 12:22pm UTC](https://discuss.elastic.co/t/max-aggregation-with-group-by-and-then-return-all-fields/172719/9 "2019-03-18T12:22:19Z")
This is the source data ``` curl -X POST "localhost:9200/lib/_doc/_bulk" -H 'Content-Type: application/json' -d' { "index":{} } {"Author": "James", "Title": "I wonder why", "Revision": 1, "PublishedDate": "2019-02-04", "Reviewer": "Mary", "ReviewComment": "needs improvement"} { "index":{} } {"Author": "James", "Title": "I wonder why", "Revision": 2, "PublishedDate": "2019-03-04", "Reviewer": "Jack", "ReviewComment": "awesome!"} { "index":{} } {"Author": "James", "Title": "I wonder why", "Revision": 2, "PublishedDate": "2019-03-04", "Reviewer": "Jones", "ReviewComment": "not bad"} { "index":{} } {"Author": "Parker", "Title": "The Endgame", "Revision": 1, "PublishedDate": "2018-12-04", "Reviewer": "Martin", "ReviewComment": "not good"} { "index":{} } {"Author": "Parker", "Title": "The Endgame", "Revision": 2, "PublishedDate": "2018-12-14", "Reviewer": "Henry", "ReviewComment": "passed"} ' ``` This is the search command ``` { "aggs" : { "groupByAuthor" : { "terms" : { "field" : "Author.keyword" }, "aggs": { "groupByTitle": { "terms" : { "field" : "Title.keyword" }, "aggs": { "groupByReviewer": { "terms": { "field": "Reviewer.keyword" }, "aggs": { "top_trades_hits": { "top_hits": { "sort": [ { "Revision": { "order": "desc" } } ], "size" : 1 } } } } } } } } } } ``` It basically output all 5 records instead of just 2nd, 3rd, 5th records (the records with max Revision group by Author and Title) ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [March 18, 2019, 12:42pm UTC](https://discuss.elastic.co/t/max-aggregation-with-group-by-and-then-return-all-fields/172719/10 "2019-03-18T12:42:09Z")
Ah OK. I misunderstood the requirement. I assumed you wanted the last comment from each reviewer. I now see you want comments from all reviewers on the last revision. How about this: ``` GET lib/_search { "size":0, "aggs" : { "groupByAuthor" : { "terms" : { "field" : "Author.keyword" }, "aggs": { "groupByTitle": { "terms" : { "field" : "Title.keyword" }, "aggs": { "groupByRevision": { "terms": { "field": "Revision", "size":1, "order": { "_term": "desc" } }, "aggs": { "top_trades_hits": { "top_hits": { "sort": [ { "Revision": { "order": "desc" } } ], "size" : 100 } } } } } } } } } } ``` ---
**Author:** ![hopeng](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hopeng/32/42195_2.png) [@hopeng](https://discuss.elastic.co/u/hopeng)\ **Post date:** [March 19, 2019, 12:02am UTC](https://discuss.elastic.co/t/max-aggregation-with-group-by-and-then-return-all-fields/172719/11 "2019-03-19T00:02:17Z")
Hi Mark, Exactly what I needed! I was evaluating whether ES can do this for the new project, if not the team will go with relational database. But now ES is more likely to be chosen! The learning curve of building ES queries is probably the 'cons' compared to RDBMS (Hopefully ES SQL support will mature soon). But this is addressed by the professionalism and swift responses of the community. Thank you very much! Regards, James ---
**Author:** ![hopeng](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hopeng/32/42195_2.png) [@hopeng](https://discuss.elastic.co/u/hopeng)\ **Post date:** [March 19, 2019, 1:17am UTC](https://discuss.elastic.co/t/max-aggregation-with-group-by-and-then-return-all-fields/172719/12 "2019-03-19T01:17:01Z")
> [@Mark\_Harwood](#): > > GET lib/\_search { "size":0, By the way, what does the initial ("size":0) do? It seems to return the same content without it. ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [March 19, 2019, 9:47am UTC](https://discuss.elastic.co/t/max-aggregation-with-group-by-and-then-return-all-fields/172719/13 "2019-03-19T09:47:10Z")
For search results elasticsearch normally returns the top 10 matching documents plus any aggregations (think of your typical e-commerce search results with top 10 matching products and summaries of options for refining by price/colour/brand). In your scenario you only want the aggregations and can dispense with the top-matching documents (so, size=0). Glad to hear you got it working ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [April 16, 2019, 9:47am UTC](https://discuss.elastic.co/t/max-aggregation-with-group-by-and-then-return-all-fields/172719/14 "2019-04-16T09:47:23Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.