# Elasticsearch Group By **URL:** \ **Category:** Elasticsearch\ **Created:** [September 28, 2022, 10:51am UTC](https://discuss.elastic.co/t/elasticsearch-group-by/315360 "2022-09-28T10:51:44Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![Ashish\_Grover](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ashish_grover/32/111387_2.png) [@Ashish\_Grover](https://discuss.elastic.co/u/Ashish_Grover)\ **Post date:** [September 28, 2022, 10:51am UTC](https://discuss.elastic.co/t/elasticsearch-group-by/315360/1 "2022-09-28T10:51:44Z")
Hi i want to convert this sql query to elasticsearch " Select field1,field2,field3,field4 From myTable Group By field1; " I want group by on field1 and on the basis of this i want field2, field3, and field4 ---
**Author:** ![RabBit\_BR](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rabbit_br/32/82261_2.png) [@RabBit\_BR](https://discuss.elastic.co/u/RabBit_BR)\ **Post date:** [September 28, 2022, 12:24pm UTC](https://discuss.elastic.co/t/elasticsearch-group-by/315360/2 "2022-09-28T12:24:51Z")
Hi @Ashish_Grover This [answer](https://stackoverflow.com/a/25990151/18778181) should work for you: ```auto { "size": 0, "aggs":{ "field_district":{ "terms": { "field": "field1", "size": 0 }, "aggs": { "tops": { "top_hits": { "size": 10 } } } } } } ``` ---
**Author:** ![Ashish\_Grover](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ashish_grover/32/111387_2.png) [@Ashish\_Grover](https://discuss.elastic.co/u/Ashish_Grover)\ **Post date:** [October 3, 2022, 1:16pm UTC](https://discuss.elastic.co/t/elasticsearch-group-by/315360/3 "2022-10-03T13:16:27Z")
Thank you bro but now i want to apply sorting on schemeName and i m getting my results like this ```auto "aggregations" : { "unique_schemename" : { "doc_count_error_upper_bound" : 0, "sum_other_doc_count" : 11, "buckets" : [ { "key" : 45, "doc_count" : 1, "unique_scheme" : { "hits" : { "total" : { "value" : 1, "relation" : "eq" }, "max_score" : 1.0, "hits" : [ { "_index" : "es_folio_master", "_type" : "_doc", "_id" : "72018968", "_score" : 1.0, "_source" : { "schid" : 45, "schemename" : "Aditya Birla SL Equity Advantage Fund Reg (G)" } } ] } } }, { "key" : 612, "doc_count" : 1, "unique_scheme" : { "hits" : { "total" : { "value" : 1, "relation" : "eq" }, "max_score" : 1.0, "hits" : [ { "_index" : "es_folio_master", "_type" : "_doc", "_id" : "72018126", "_score" : 1.0, "_source" : { "schid" : 612, "schemename" : "Tata India Tax Saving Fund Reg IDCW" } } ] } } }, { "key" : 1013, "doc_count" : 1, "unique_scheme" : { "hits" : { "total" : { "value" : 1, "relation" : "eq" }, "max_score" : 1.0, "hits" : [ { "_index" : "es_folio_master", "_type" : "_doc", "_id" : "71911983", "_score" : 1.0, "_source" : { "schid" : 1013, "schemename" : "IDFC Money Manager Fund Reg (G)" } } ] } } }, { "key" : 1254, "doc_count" : 1, "unique_scheme" : { "hits" : { "total" : { "value" : 1, "relation" : "eq" }, "max_score" : 1.0, "hits" : [ { "_index" : "es_folio_master", "_type" : "_doc", "_id" : "71992566", "_score" : 1.0, "_source" : { "schid" : 1254, "schemename" : "LIC Savings Fund (G)" } } ] } } }, { "key" : 2683, "doc_count" : 1, "unique_scheme" : { "hits" : { "total" : { "value" : 1, "relation" : "eq" }, "max_score" : 1.0, "hits" : [ { "_index" : "es_folio_master", "_type" : "_doc", "_id" : "71983409", "_score" : 1.0, "_source" : { "schid" : 2683, "schemename" : "UTI Value Opportunities Fund (G)" } } ] } } }, { "key" : 2756, "doc_count" : 1, "unique_scheme" : { "hits" : { "total" : { "value" : 1, "relation" : "eq" }, "max_score" : 1.0, "hits" : [ { "_index" : "es_folio_master", "_type" : "_doc", "_id" : "71989204", "_score" : 1.0, "_source" : { "schid" : 2756, "schemename" : "Franklin India Liquid Fund Super Ins (G)" } } ] } } }, { "key" : 3600, "doc_count" : 1, "unique_scheme" : { "hits" : { "total" : { "value" : 1, "relation" : "eq" }, "max_score" : 1.0, "hits" : [ { "_index" : "es_folio_master", "_type" : "_doc", "_id" : "71934889", "_score" : 1.0, "_source" : { "schid" : 3600, "schemename" : "UTI CCF Savings Plan (G)" } } ] } } }, { "key" : 4068, "doc_count" : 1, "unique_scheme" : { "hits" : { "total" : { "value" : 1, "relation" : "eq" }, "max_score" : 1.0, "hits" : [ { "_index" : "es_folio_master", "_type" : "_doc", "_id" : "71991934", "_score" : 1.0, "_source" : { "schid" : 4068, "schemename" : "ICICI Pru Balanced Advantage Fund Reg (G)" } } ] } } }, { "key" : 14315, "doc_count" : 1, "unique_scheme" : { "hits" : { "total" : { "value" : 1, "relation" : "eq" }, "max_score" : 1.0, "hits" : [ { "_index" : "es_folio_master", "_type" : "_doc", "_id" : "71898265", "_score" : 1.0, "_source" : { "schid" : 14315, "schemename" : "Nippon India Large Cap Fund IDCW" } } ] } } }, { "key" : 14641, "doc_count" : 1, "unique_scheme" : { "hits" : { "total" : { "value" : 1, "relation" : "eq" }, "max_score" : 1.0, "hits" : [ { "_index" : "es_folio_master", "_type" : "_doc", "_id" : "72018125", "_score" : 1.0, "_source" : { "schid" : 14641, "schemename" : "SBI Magnum Low Duration Fund Reg (G)" } } ] } } } ] } } } ``` ---
**Author:** ![RabBit\_BR](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rabbit_br/32/82261_2.png) [@RabBit\_BR](https://discuss.elastic.co/u/RabBit_BR)\ **Post date:** [October 3, 2022, 3:00pm UTC](https://discuss.elastic.co/t/elasticsearch-group-by/315360/4 "2022-10-03T15:00:09Z")
[TopHits](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-top-hits-aggregation.html) has sort function, maybe help you. ---
**Author:** ![Ashish\_Grover](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ashish_grover/32/111387_2.png) [@Ashish\_Grover](https://discuss.elastic.co/u/Ashish_Grover)\ **Post date:** [October 4, 2022, 7:48am UTC](https://discuss.elastic.co/t/elasticsearch-group-by/315360/5 "2022-10-04T07:48:19Z")
Sorry to bother u again i have tried sort function of top hits like this ````auto "unique_schemename": { "terms": { "field": "schid", "size": 10 }, "aggs": { "unique_scheme": { "top_hits": { "sort": [{ "schemename.keyword": {"order": "desc"} }], "_source": [ "schid", "schemename" ], "size": 1 } } } } },``` And i got results like this ```"aggregations" : { "unique_schemename" : { "doc_count_error_upper_bound" : 0, "sum_other_doc_count" : 11, "buckets" : [ { "key" : 45, "doc_count" : 1, "unique_scheme" : { "hits" : { "total" : { "value" : 1, "relation" : "eq" }, "max_score" : null, "hits" : [ { "_index" : "es_folio_master", "_type" : "_doc", "_id" : "72018968", "_score" : null, "_source" : { "schid" : 45, "schemename" : "Aditya Birla SL Equity Advantage Fund Reg (G)" }, "sort" : [ "Aditya Birla SL Equity Advantage Fund Reg (G)" ] } ] } } }, { "key" : 612, "doc_count" : 1, "unique_scheme" : { "hits" : { "total" : { "value" : 1, "relation" : "eq" }, "max_score" : null, "hits" : [ { "_index" : "es_folio_master", "_type" : "_doc", "_id" : "72018126", "_score" : null, "_source" : { "schid" : 612, "schemename" : "Tata India Tax Saving Fund Reg IDCW" }, "sort" : [ "Tata India Tax Saving Fund Reg IDCW" ] } ] } } }, { "key" : 1013, "doc_count" : 1, "unique_scheme" : { "hits" : { "total" : { "value" : 1, "relation" : "eq" }, "max_score" : null, "hits" : [ { "_index" : "es_folio_master", "_type" : "_doc", "_id" : "71911983", "_score" : null, "_source" : { "schid" : 1013, "schemename" : "IDFC Money Manager Fund Reg (G)" }, "sort" : [ "IDFC Money Manager Fund Reg (G)" ] } ] } } }, { "key" : 1254, "doc_count" : 1, "unique_scheme" : { "hits" : { "total" : { "value" : 1, "relation" : "eq" }, "max_score" : null, "hits" : [ { "_index" : "es_folio_master", "_type" : "_doc", "_id" : "71992566", "_score" : null, "_source" : { "schid" : 1254, "schemename" : "LIC Savings Fund (G)" }, "sort" : [ "LIC Savings Fund (G)" ] } ] } } }, { "key" : 2683, "doc_count" : 1, "unique_scheme" : { "hits" : { "total" : { "value" : 1, "relation" : "eq" }, "max_score" : null, "hits" : [ { "_index" : "es_folio_master", "_type" : "_doc", "_id" : "71983409", "_score" : null, "_source" : { "schid" : 2683, "schemename" : "UTI Value Opportunities Fund (G)" }, "sort" : [ "UTI Value Opportunities Fund (G)" ] } ] } } }, { "key" : 2756, "doc_count" : 1, "unique_scheme" : { "hits" : { "total" : { "value" : 1, "relation" : "eq" }, "max_score" : null, "hits" : [ { "_index" : "es_folio_master", "_type" : "_doc", "_id" : "71989204", "_score" : null, "_source" : { "schid" : 2756, "schemename" : "Franklin India Liquid Fund Super Ins (G)" }, "sort" : [ "Franklin India Liquid Fund Super Ins (G)" ] } ] } } }, { "key" : 3600, "doc_count" : 1, "unique_scheme" : { "hits" : { "total" : { "value" : 1, "relation" : "eq" }, "max_score" : null, "hits" : [ { "_index" : "es_folio_master", "_type" : "_doc", "_id" : "71934889", "_score" : null, "_source" : { "schid" : 3600, "schemename" : "UTI CCF Savings Plan (G)" }, "sort" : [ "UTI CCF Savings Plan (G)" ] } ] } } }, { "key" : 4068, "doc_count" : 1, "unique_scheme" : { "hits" : { "total" : { "value" : 1, "relation" : "eq" }, "max_score" : null, "hits" : [ { "_index" : "es_folio_master", "_type" : "_doc", "_id" : "71991934", "_score" : null, "_source" : { "schid" : 4068, "schemename" : "ICICI Pru Balanced Advantage Fund Reg (G)" }, "sort" : [ "ICICI Pru Balanced Advantage Fund Reg (G)" ] } ] } } }, { "key" : 14315, "doc_count" : 1, "unique_scheme" : { "hits" : { "total" : { "value" : 1, "relation" : "eq" }, "max_score" : null, "hits" : [ { "_index" : "es_folio_master", "_type" : "_doc", "_id" : "71898265", "_score" : null, "_source" : { "schid" : 14315, "schemename" : "Nippon India Large Cap Fund IDCW" }, "sort" : [ "Nippon India Large Cap Fund IDCW" ] } ] } } }, { "key" : 14641, "doc_count" : 1, "unique_scheme" : { "hits" : { "total" : { "value" : 1, "relation" : "eq" }, "max_score" : null, "hits" : [ { "_index" : "es_folio_master", "_type" : "_doc", "_id" : "72018125", "_score" : null, "_source" : { "schid" : 14641, "schemename" : "SBI Magnum Low Duration Fund Reg (G)" }, "sort" : [ "SBI Magnum Low Duration Fund Reg (G)" ] } ] } } } ] } } }``` Here i m not getting results in sorted manner I "Tata India Tax Saving Fund Reg IDCW" this schemename is before "IDFC Money Manager Fund Reg (G)" Is it possible to get all schemes sorted ```` ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [November 1, 2022, 7:49am UTC](https://discuss.elastic.co/t/elasticsearch-group-by/315360/6 "2022-11-01T07:49:07Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.