x

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

more ▼

asked Nov 29, 2012 at 05:55 PM in Default

avatar image

tombiernacki
338 20 22 27

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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)

more ▼

answered Nov 29, 2012 at 06:03 PM

avatar image

mjharper
2k 3 8 14

changing Month to be tinyint fix my issues. Thanks

Nov 29, 2012 at 06:24 PM tombiernacki
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Nov 29, 2012 at 06:07 PM

avatar image

Grant Fritchey ♦♦
137k 20 43 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x434
x28

asked: Nov 29, 2012 at 05:55 PM

Seen: 1097 times

Last Updated: Nov 30, 2012 at 02:32 AM

Copyright 2017 Redgate Software. Privacy Policy