# Canves, SQL can not order my query

**URL:** <https://discuss.elastic.co/t/canves-sql-can-not-order-my-query/225287>\
**Category:** Kibana\
**Tags:** canvas\
**Created:** [March 26, 2020, 8:42pm UTC](https://discuss.elastic.co/t/canves-sql-can-not-order-my-query/225287 "2020-03-26T20:42:07Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![emad101](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/emad101/32/82338_2.png) [@emad101](https://discuss.elastic.co/u/emad101)\
**Post date:** [March 26, 2020, 8:42pm UTC](https://discuss.elastic.co/t/canves-sql-can-not-order-my-query/225287/1 "2020-03-26T20:42:07Z")

</div>

Hello,  
I wrote the following query in Canves

> SELECT (app\_code) AS appcodex , COUNT(DISTINCT component\_name) AS total, (COUNT(DISTINCT component\_name)/(COUNT(iipm.it\_executive))_100) As p FROM "devops-ucd_\*" WHERE iipm.l3\_it\_head = 'name' GROUP BY appcodex ORDER BY (p) DESC  
> LIMIT 6

**I am trying to order the table by percentage, but I keep receiving this error**

> Cannot order by non-grouped column [p], expected [app\_code] or an aggregate function

Here is how my query results look like

 ![Screen Shot 2020-03-26 at 4.40.31 PM](https://us1.discourse-cdn.com/elastic/original/3X/e/2/e2eb89e47fd6b6257666c6f42955f1cbd1315287.png)

I am not sure how to fix this issue, any help would be appreciated?

---

<div class="post-metadata">

**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\
**Post date:** [March 27, 2020, 1:45pm UTC](https://discuss.elastic.co/t/canves-sql-can-not-order-my-query/225287/2 "2020-03-27T13:45:11Z")

</div>

It makes sense to ask this specific question in the Elasticsearch forum because it's an Elasticsearch SQL problem, but I think in this scenario you can only order by either a grouped column or a single aggregate function.

> SELECT (app\_code) AS appcodex , COUNT(DISTINCT component\_name) AS total, (COUNT(DISTINCT component\_name)/(COUNT(iipm.it\_executive)) _100) As percentage FROM "devops-ucd_ \*" WHERE iipm.l3\_it\_head = 'Kazmi, Naim' GROUP BY appcodex ORDER BY COUNT(DISTINCT component\_name) DESC  
> LIMIT 6

would work, but that's probably not helpful in this scenario.

If you have a low (low in maybe \< 1000) number of `appcodex` terms you could move the sorting and limiting operation to the client like this:

```auto
essql query="SELECT (app_code) AS appcodex , COUNT(DISTINCT component_name) AS total, (COUNT(DISTINCT component_name)/(COUNT(iipm.it_executive))100) As percentage FROM \"devops-ucd*\" WHERE iipm.l3_it_head = 'Kazmi, Naim' GROUP BY appcodex"
| sort by="percentage" reverse=true 
| head count=6
| table
| render

```

This will send the complete table to the client and sort there with the `sort` canvas function and a subsequent `head` to just take the top 6 values.

---

<div class="post-metadata">

**Author:** ![emad101](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/emad101/32/82338_2.png) [@emad101](https://discuss.elastic.co/u/emad101)\
**Post date:** [March 27, 2020, 2:39pm UTC](https://discuss.elastic.co/t/canves-sql-can-not-order-my-query/225287/3 "2020-03-27T14:39:55Z")

</div>

> [@flash1293](#):
>
> | sort by="percentage" reverse=true | head count=6

Thank you, that is really helpful

---

<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:** [April 24, 2020, 2:40pm UTC](https://discuss.elastic.co/t/canves-sql-can-not-order-my-query/225287/4 "2020-04-24T14:40:08Z")

</div>

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