question

tombiernacki avatar image
tombiernacki asked

order by month tsql

I need help setting an order of my column by the month. February is always by first month. the number for february is 2. The order is fine until i get to October which the number is 10. It becomes the first row in the column. Any ideas? my select statement is... select * from (select MONTH,PayerType,NetRevenue,YearToDate,Planned from @Results) as p pivot (sum(NetRevenue) for PayerType in ([A],[ B])) as PayerTypeNetRev order by Month asc,[A],[B],YearToDate,Planned MONTH YearToDate Planned A B 10 1571.37 14167.12 206.80 1364.57 2 12509.22 14167.12 1461.35 9476.50 3 20654.06 14167.12 373.04 7771.80 4 39580.43 14167.12 5128.94 13797.43 5 55884.42 14167.12 3886.74 12417.25 6 61477.66 14167.12 0.00 5593.24 7 67731.24 14167.12 995.38 5258.20 8 78568.53 14167.12 1011.52 9825.77 9 88678.96 14167.12 1597.51 8512.92
tsqlorder-by
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

mjharper avatar image
mjharper answered
It's treating the Month column as text. So if we took all the months of the year it would return in the order 1,10,11,12,2,3,4,5,6,7,8,9 You will either need to change the column to be an integer (or TINYINT will do for months) or replace the ORDER BY month with ORDER BY CAST(Month as INT)
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

changing Month to be tinyint fix my issues. Thanks
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
Doing the function on the ORDER BY could cause issues with performance. Another option is to have a Dates table (call it something else) that you can join to for stuff like the number of the month. Then you can order by a value that is joined instead of running a function. That can run much faster.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.