# How to do group by multiple fields and sort on a different field, in elasticsearch

**URL:** <https://discuss.elastic.co/t/how-to-do-group-by-multiple-fields-and-sort-on-a-different-field-in-elasticsearch/207963>\
**Category:** Elasticsearch\
**Created:** [November 14, 2019, 10:03pm UTC](https://discuss.elastic.co/t/how-to-do-group-by-multiple-fields-and-sort-on-a-different-field-in-elasticsearch/207963 "2019-11-14T22:03:30Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Seetha93](https://avatars.discourse-cdn.com/v4/letter/s/ecc23a/32.png) [@Seetha93](https://discuss.elastic.co/u/Seetha93)\
**Post date:** [November 14, 2019, 10:03pm UTC](https://discuss.elastic.co/t/how-to-do-group-by-multiple-fields-and-sort-on-a-different-field-in-elasticsearch/207963/1 "2019-11-14T22:03:30Z")

</div>

Lets say I have a query like

```
Select * from table
Group By field_1, field_2, field_3
Order By filed_7;

```

How to achieve the same functionality in elasticsearch.

I use **Composite Aggregations** to do **Group by multiple fields**. Is it possible to do the **sorting** part?

---

<div class="post-metadata">

**Author:** ![matriv](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/matriv/32/43656_2.png) [@matriv](https://discuss.elastic.co/u/matriv)\
**Post date:** [November 19, 2019, 1:58pm UTC](https://discuss.elastic.co/t/how-to-do-group-by-multiple-fields-and-sort-on-a-different-field-in-elasticsearch/207963/2 "2019-11-19T13:58:23Z")

</div>

With Composite Aggregations you can only sort by the grouped by fields (you can only change to descending order if you wish).

But just to clarify, why do you want to ORDER BY a field that it's not in the group by or the select list?  
This query is not a valid SQL query, PostgreSQL for example will return:

```auto
column "field_7" must appear in the GROUP BY clause or be used in an aggregate function

```

---

<div class="post-metadata">

**Author:** ![Seetha93](https://avatars.discourse-cdn.com/v4/letter/s/ecc23a/32.png) [@Seetha93](https://discuss.elastic.co/u/Seetha93)\
**Post date:** [November 25, 2019, 6:54pm UTC](https://discuss.elastic.co/t/how-to-do-group-by-multiple-fields-and-sort-on-a-different-field-in-elasticsearch/207963/3 "2019-11-25T18:54:12Z")

</div>

That query was just to give an idea in query terms. And thanks for the input.

---

<div class="post-metadata">

**Author:** ![matriv](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/matriv/32/43656_2.png) [@matriv](https://discuss.elastic.co/u/matriv)\
**Post date:** [November 26, 2019, 11:03am UTC](https://discuss.elastic.co/t/how-to-do-group-by-multiple-fields-and-sort-on-a-different-field-in-elasticsearch/207963/4 "2019-11-26T11:03:08Z")

</div>

Composite aggs through ES API don't allow to group by anything else than the group by fields  
ES SQL API though allows you to also order on aggregate function:

e.g.:

```auto
SELECT field_1, count(*) as cnt
FROM test
GROUP BY field_1
ORDER BY cnt

```

---

<div class="post-metadata">

**Author:** ![Seetha93](https://avatars.discourse-cdn.com/v4/letter/s/ecc23a/32.png) [@Seetha93](https://discuss.elastic.co/u/Seetha93)\
**Post date:** [November 26, 2019, 7:21pm UTC](https://discuss.elastic.co/t/how-to-do-group-by-multiple-fields-and-sort-on-a-different-field-in-elasticsearch/207963/5 "2019-11-26T19:21:22Z")

</div>

> [@matriv](#):
>
> ES SQL API

Thanks @matriv. We are using elasticsearch hosted on AWS, so I afraid I cant use ES SQL API for my purpose. But good to know. I appreciate it

---

<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:** [December 24, 2019, 7:21pm UTC](https://discuss.elastic.co/t/how-to-do-group-by-multiple-fields-and-sort-on-a-different-field-in-elasticsearch/207963/6 "2019-12-24T19:21:25Z")

</div>

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