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
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)
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 People are following this question.
