CODEWITHSUNDEEP
sqlsql-server
rp4361
rp4361

Reputation: 445

sql server order by clause

Below is my query:

SELECT * FROM [TEMPDB].[dbo].[##TEMPMSICHARTFORMATTED]

It gives me following result:

query result

The problem is if I am doing order by MonthCycle then since it is a string it is using month's first letter to sort, but I want an order by on the basis of month like Jan should be first, then Feb and so on.

Upvotes: 0

Views: 63

Answers (5)

George Dando
George Dando

Reputation: 444

The question doesn't specify if the report will be spanning several years, but if so I'd use something like this:

ORDER BY CONVERT(DATETIME, '1 ' + REPLACE(monthCycle, '''', ' '))

Upvotes: 2

gofr1
gofr1

Reputation: 15977

And another way :)

DECLARE @d as date = '2016-01-01'
;WITH months AS (
SELECT  1 as [Month],
        LEFT(DATENAME(Month,@d),3) as MName
UNION ALL
SELECT  [Month] + 1,
        LEFT(DATENAME(Month,DATEADD(Month,[Month],@d)) ,3) 
FROM months 
WHERE [Month] + 1 <= 12
)

SELECT c.*
FROM [TEMPDB].[dbo].[##TEMPMSICHARTFORMATTED] c
LEFT JOIN months m 
    ON c.MONTHCYCLE LIKE m.MName +'%'
ORDER BY RIGHT(c.MONTHCYCLE,2) ASC, m.[Month] ASC

Output:

MONTHCYCLE TARGET Corp Stnd   Local       OF Stnd
---------- ------ ----------- ----------- -----------
Jan'16            2           1           1
Feb'16            1           1           1
Mar'16            2           3           1
Apr'16            4           3           NULL

(4 row(s) affected)

Upvotes: 0

A_Sk
A_Sk

Reputation: 4630

You can Use DATEPART

Something like this,

SELECT * FROM [TEMPDB].[dbo].[##TEMPMSICHARTFORMATTED] Order by DATEPART(MM,'01'+MONTHCYCLE)

OR

SELECT * FROM [TEMPDB].[dbo].[##TEMPMSICHARTFORMATTED] Order by DATEPART(MM,'01'+Replace(MONTHCYCLE,'''',''))

Upvotes: 1

Cetin Basoz
Cetin Basoz

Reputation: 23797

SELECT * FROM [TEMPDB].[dbo].[##TEMPMSICHARTFORMATTED]
ORDER BY CASE 
  WHEN LEFT(MONTHCYCLE,3) = 'Jan' then 1
  WHEN LEFT(MONTHCYCLE,3) = 'Feb' then 2
  WHEN LEFT(MONTHCYCLE,3) = 'Mar' then 3
  WHEN LEFT(MONTHCYCLE,3) = 'Apr' then 4
  WHEN LEFT(MONTHCYCLE,3) = 'May' then 5
  WHEN LEFT(MONTHCYCLE,3) = 'Jun' then 6
  WHEN LEFT(MONTHCYCLE,3) = 'Jul' then 7
  WHEN LEFT(MONTHCYCLE,3) = 'Aug' then 8
  WHEN LEFT(MONTHCYCLE,3) = 'Sep' then 9
  WHEN LEFT(MONTHCYCLE,3) = 'Oct' then 10
  WHEN LEFT(MONTHCYCLE,3) = 'Nov' then 11
  WHEN LEFT(MONTHCYCLE,3) = 'Dec' then 12
  ELSE 0 END;

Upvotes: 1

jarlh
jarlh

Reputation: 44696

Have a case expression that returns the month number:

SELECT * FROM [TEMPDB].[dbo].[##TEMPMSICHARTFORMATTED]
order by case left(monthcycle,3) when 'Jan' then 1
                                 when 'Feb' then 2
                                 ...
         end

Upvotes: 2

Related Questions