# Elasticsearch SQL ORDER BY Month
**URL:** https://discuss.elastic.co/t/elasticsearch-sql-order-by-month/256716
**Category:** Elasticsearch
**Tags:** elastic-stack-sql
**Created:** [November 25, 2020, 10:00pm UTC](https://discuss.elastic.co/t/elasticsearch-sql-order-by-month/256716 "2020-11-25T22:00:02Z")
**Posts on this page:** 9
**Page:** 1
### Author:  [@willemdh](https://discuss.elastic.co/u/willemdh)
#### Post date: [November 25, 2020, 10:00pm UTC](https://discuss.elastic.co/t/elasticsearch-sql-order-by-month/256716/1 "2020-11-25T22:00:02Z")
Hello,
I'm almost there getting my vertical bar to work in Canvas based on a SQL query, but I'm having a hard time ordering the monthly buckets chronologically. My SQL query:
```
SELECT COUNT(DISTINCT user.name) AS UniqueUsers,
MONTH_NAME("@timestamp") AS Month
FROM "nagios" WHERE "@timestamp" > now() - interval 1 years AND event.dataset LIKE 'nagios.audit' AND MATCH(message,'Logged in')
GROUP BY Month
ORDER BY Month
```
Result:
So how can I get the months ordered by @timestamp?
Grtz
Willem
---
### Author:  [@bogdan.pintea](https://discuss.elastic.co/u/bogdan.pintea)
#### Post date: [November 27, 2020, 9:03am UTC](https://discuss.elastic.co/t/elasticsearch-sql-order-by-month/256716/2 "2020-11-27T09:03:54Z")
Instead/besides `MONTH_NAME` you could GROUP BY [MONTH](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-functions-datetime.html#sql-functions-datetime-month).
---
### Author:  [@willemdh](https://discuss.elastic.co/u/willemdh)
#### Post date: [November 27, 2020, 9:48am UTC](https://discuss.elastic.co/t/elasticsearch-sql-order-by-month/256716/3 "2020-11-27T09:48:02Z")
Hello @bogdan.pintea,
Thanks a lot for your answer. In the meantime we are +- using something similar to what you suggest:
```
SELECT COUNT(DISTINCT user.name) AS UniqueUsers,
MONTH_NAME("@timestamp") AS Month,
MONTH("@timestamp") AS MonthNumber
FROM "nagios" WHERE "@timestamp" > now() - interval 1 years AND event.dataset LIKE 'nagios.audit' AND MATCH(message,'Logged in')
GROUP BY Month, MonthNumber
ORDER BY MonthNumber
```
The result is:
But as you can see the graph start with month 1 (january). As it's november currently, the first bucket in the graph should be december last year. I think we should somehow evolve to a group by / sort based on a combination of year and month. But not sure how to accomplish that.
Grtz
Willem
---
### Author:  [@willemdh](https://discuss.elastic.co/u/willemdh)
#### Post date: [December 16, 2020, 9:27am UTC](https://discuss.elastic.co/t/elasticsearch-sql-order-by-month/256716/4 "2020-12-16T09:27:50Z")
(Trying to prevent auto close) So ordering buckets chronologically with essql is possible or not?
---
### Author:  [@bogdan.pintea](https://discuss.elastic.co/u/bogdan.pintea)
#### Post date: [December 16, 2020, 10:36am UTC](https://discuss.elastic.co/t/elasticsearch-sql-order-by-month/256716/5 "2020-12-16T10:36:17Z")
@willemdh, maybe something like this would work for you?
```auto
SELECT COUNT(DISTINCT user.name), DATETIME_FORMAT("@timestamp", 'MMM')
FROM ...
WHERE ...
GROUP BY DATETIME_FORMAT("@timestamp", 'yyyy-LL'), 2
ORDER BY ...
```
---
### Author:  [@willemdh](https://discuss.elastic.co/u/willemdh)
#### Post date: [December 16, 2020, 6:18pm UTC](https://discuss.elastic.co/t/elasticsearch-sql-order-by-month/256716/6 "2020-12-16T18:18:48Z")
Thanks @bogdan.pintea for your answer.
The data preview seems perfect:
But I'm having some troubles configuring the graph when I don't use as 'AS', seems something wrong with using "@timestamp".
So tried with 'AS':
```
SELECT COUNT(DISTINCT user.name) AS UniqueUsers, DATETIME_FORMAT("@timestamp", 'MMM') AS Month
FROM "nagios"
WHERE "@timestamp" > now() - interval 1 years AND event.dataset LIKE 'nagios.audit' AND MATCH(message,'Logged in')
GROUP BY DATETIME_FORMAT("@timestamp", 'yyyy-LL'), 2
```
Which produces:
But this is also ordered alphabetically..
So it seems to result in the same issue using "GROUP BY Month, MonthNumber".
Willem
---
### Author:  [@bogdan.pintea](https://discuss.elastic.co/u/bogdan.pintea)
#### Post date: [December 17, 2020, 12:13pm UTC](https://discuss.elastic.co/t/elasticsearch-sql-order-by-month/256716/7 "2020-12-17T12:13:02Z")
I see.
This seems to be a Canvas-specific behaviour. My Canvas kung-fu is not that strong unfortunately, but while trying to replicate your experience I've noticed that a `null` X-axis value will trigger the plot to reorder the X values. I don't see a `null` value in your list, but if you think there is - and just not rendered - you might want to try filtering it out: add a `AND Month IS NOT NULL` to your WHERE clause.
You could try to play with the expression in the ` Expression editor` and potentially add a `| head count=` (or `| tail ..`) after `| essql ` function to try find out if there is a value triggering the reorder.
Even better, maybe ask in the Kibana forum.
You could also try to generate a name for the months which is immutable to reordering, but I feel there should be an easier way to get what you want.
---
### Author:  [@willemdh](https://discuss.elastic.co/u/willemdh)
#### Post date: [December 18, 2020, 7:57am UTC](https://discuss.elastic.co/t/elasticsearch-sql-order-by-month/256716/8 "2020-12-18T07:57:10Z")
Created [Canvas Group By Month with Chronological order](https://discuss.elastic.co/t/canvas-group-by-month-with-chronological-order/259066)
Sorry for the many questions and ty very much for the insights @bogdan.pintea
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [January 15, 2021, 7:57am UTC](https://discuss.elastic.co/t/elasticsearch-sql-order-by-month/256716/9 "2021-01-15T07:57:13Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.