Chart Sorting in SQL

Grafana
1

{1CA9D090-BE4E-487C-86E5-4641DE939CD3}
{1CA9D090-BE4E-487C-86E5-4641DE939CD3}1129×304 12.1 KB

In this code, how can I sort this correctly, if I remove month name, it sorts correctly, but I want this visible. Thanks!

SELECT DISTINCT
Month(HPD:Help Desk.Submit Date) +‘-’+datename(‘mm’,HPD:Help Desk.Submit Date) AS MonthYear
[…]
Group By
MonthYear

2 
Order by Month(`HPD:Help Desk` .`Submit Date` )
3

Thaks, but doing this just causes an error: 895: Sort Item must exist in Selection list if distinct is specified.

I can sort by Month(HPD:Help Desk .Submit Date ) +‘-’+datename(‘mm’,HPD:Help Desk .Submit Date ), but that causes the same sorting issue.

1 Like
4

please post the full query with the aggregation function

5

SELECT DISTINCT
Month(HPD:Help Desk.Submit Date)+’ - '+datename(‘mm’,HPD:Help Desk.Submit Date) AS MonthYear
,
SUM(CASE
WHEN HPD:Help Desk.Submit Date >= FIRSTDAYOF(‘month’,(${__from})/1000) THEN 1
ELSE 0
END )AS SubmttedTickets,
SUM (CASE
WHEN HPD:Help Desk.Last Resolved Date >= FIRSTDAYOF(‘month’,(${__from})/1000) THEN 1
ELSE 0
END )AS ResolvedTickets
FROM AR System Schema.HPD:Help Desk
WHERE (
HPD:Help Desk.Incident Number IS NOT NULL
)
AND (
(
HPD:Help Desk.Submit Date BETWEEN (${__from})/1000 AND (${__to})/1000
OR HPD:Help Desk.Last Resolved Date BETWEEN (${__from})/1000 AND (${__to})/1000
)
AND HPD:Help Desk.Assigned Group IN (‘group1’, ‘group2’)
)

Group By
MonthYear

6

basically your issue is not grafana issue. Can you do this?

select * from (
SELECT DISTINCT
Month(HPD:Help Desk.Submit Date)
+’ - '+
datename(‘mm’,HPD:Help Desk.Submit Date) AS MonthYear
,
----
Group By
MonthYear
) a
--here just capture the numeric month 
order by cast(substring(MonthYear, 0,??) as int)

not sure if you are on mysql or mssql?