Order by Month properly not alphabetical

Question

Post reply

Order by Month properly not alphabetical

MrCapuchino

Mr or Mrs. 500

Points: 535
More actions
July 26, 2010 at 2:35 pm

#225392

Hi,
I've been searching to do this but haven't found the answer, the closer I can go is ordering by proper month but instead of showing the month name it gives it to me in numbers lik (1, 2, 3, 4) I want (january, february, march)
Here is what Ive done so far
To get the name of the month one part of my query says
datename(month, date) as month
then I'm trying to order it like
ORDER BY month
but the result ends up ordering the month in alphabetical order.
Any ideas?
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Nevyn SSChampion Points: 14210 More actions · Answer 1

With "Order by" it does not need to be something in your select list.

So you can select datename(month,YOURFIELD) and order by datepart(month,YOURFIELD)

MrCapuchino Mr or Mrs. 500 Points: 535 More actions · Answer 2

Hi,

The thing is that the query I use has a GROUP BY clause because it has to get data from different referenced tables. In fact I'm grouping by months, but these ones come ordered alphabetically.

What can I do?

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 3

For a GROUP BY query, you can only order by columns in the SELECT list. Therefore the only way to do what you need is to put the month datepart into the select list and order by that number. You can't do it by the alphanumeric name.

MrCapuchino Mr or Mrs. 500 Points: 535 More actions · Answer 4

Mmmm... that's sad

I guess I will use a temp table or something like that, thanks

emily-1119612 SSCarpal Tunnel Points: 4166 More actions · Answer 5

Can you use a subquery?

SELECT

Field1, Field2 FROM

(

Your query here with your groupings

) AS MyAlias

ORDER BY whatever you want

This is of course air code and not guaranteed to work...

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

In this example, I group by the first day of the month, order by that value, and extract year and month from that value.

select

[Year]= year(dateadd(mm,datediff(mm,0,Mydate),0)),

MonthName= datename(month,dateadd(mm,datediff(mm,0,Mydate),0)),

[RowCount]= count(*)

from

( -- Random Test Dates

select top 1000 mydate = crdate from sysobjects order by newid()

) a

group by

dateadd(mm,datediff(mm,0,Mydate),0)

order by

dateadd(mm,datediff(mm,0,Mydate),0)

Results:

Year MonthName RowCount

----------- ------------------------------ -----------

2003 April 1

2005 October 46

2007 November 46

2008 March 39

2008 September 36

2010 April 71

2010 May 13

2010 June 13

2010 July 9

(9 row(s) affected)

lthamanam Valued Member Points: 65 More actions · Answer 7

hi,

try the following query

SELECT DATENAME(month, GETDATE()) AS 'Month Name'