# Did I stumble across another bug? SQL select ordering on 2 fields

**URL:** <https://discuss.elastic.co/t/did-i-stumble-across-another-bug-sql-select-ordering-on-2-fields/212317>\
**Category:** Elasticsearch\
**Tags:** elastic-stack-sql\
**Created:** [December 18, 2019, 12:15pm UTC](https://discuss.elastic.co/t/did-i-stumble-across-another-bug-sql-select-ordering-on-2-fields/212317 "2019-12-18T12:15:19Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Skeeve](https://avatars.discourse-cdn.com/v4/letter/s/e480ec/32.png) [@Skeeve](https://discuss.elastic.co/u/Skeeve)\
**Post date:** [December 18, 2019, 12:15pm UTC](https://discuss.elastic.co/t/did-i-stumble-across-another-bug-sql-select-ordering-on-2-fields/212317/1 "2019-12-18T12:15:19Z")

</div>

I tried to order an SQL query result using two fields, but it seems the ordering is only done on one field.

Example data:

```auto
PUT /myindex
POST _bulk
{ "index": { "_index": "myindex" }}
{ "traphit": 1, "customer": "A", "domain": "a1" }
{ "index": { "_index": "myindex" }}
{ "traphit": 2, "customer": "A", "domain": "a2" }
{ "index": { "_index": "myindex" }}
{ "traphit": 3, "customer": "C", "domain": "a3" }
{ "index": { "_index": "myindex" }}
{ "traphit": 4, "customer": "B", "domain": "b1" }
{ "index": { "_index": "myindex" }}
{ "traphit": 5, "customer": "B", "domain": "b2" }
{ "index": { "_index": "myindex" }}
{ "traphit": 6, "customer": "A", "domain": "b3" }
{ "index": { "_index": "myindex" }}
{ "traphit": 7, "customer": "C", "domain": "c1" }
{ "index": { "_index": "myindex" }}
{ "traphit": 8, "customer": "C", "domain": "c2" }
{ "index": { "_index": "myindex" }}
{ "traphit": 9, "customer": "B", "domain": "c3" }

```

The query is

```auto
POST _sql?format=txt
{
  "query":"""
    select sum(traphit) as traphits
         , customer
         , domain
    from myindex
    group by customer, domain
    having traphits > 0
    order by customer asc, traphits desc
  """
}

```

The result is

```auto
   traphits | customer | domain     
---------------+---------------+---------------
9.0 |B |c3             
8.0 |C |c2             
7.0 |C |c1             
6.0 |A |b3             
5.0 |B |b2             
4.0 |B |b1             
3.0 |C |a3             
2.0 |A |a2             
1.0 |A |a1             

```

While it should be

```auto
   traphits | customer | domain     
---------------+---------------+---------------
6.0 |A |b3             
2.0 |A |a2             
1.0 |A |a1             
9.0 |B |c3             
5.0 |B |b2             
4.0 |B |b1             
8.0 |C |c2             
7.0 |C |c1             
3.0 |C |a3             

```

---

<div class="post-metadata">

**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\
**Post date:** [December 19, 2019, 10:10am UTC](https://discuss.elastic.co/t/did-i-stumble-across-another-bug-sql-select-ordering-on-2-fields/212317/2 "2019-12-19T10:10:46Z")

</div>

@Skeeve thank you for posting. You've actually uncovered a bug. I created an issue [here](https://github.com/elastic/elasticsearch/issues/50355), where there are more details about 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:** [January 16, 2020, 10:10am UTC](https://discuss.elastic.co/t/did-i-stumble-across-another-bug-sql-select-ordering-on-2-fields/212317/3 "2020-01-16T10:10:48Z")

</div>

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