Ordering of month/year pairs in T-SQL query

Question

I am writing a stored procedure for displaying month and year. It is working, but it is not returning the rows in the desired order.

ALTER procedure [dbo].[audioblog_getarchivedates]  
as  
begin  
select DateName(Month,a.createddate) + ' ' + DateName(Year,a.createddate) as ArchiveDate 
from audio_blog a 
group by DateName(Month,a.createddate) + ' ' + DateName(Year,a.createddate) 
order by DateName(Month,a.createddate) + ' ' + DateName(Year,a.createddate) desc
end

Results will come like this:

March 2010
January 2010
February 2010

But that is not in a order (desc).

Answer 1

End it with:

order by a.createddate desc

Answer 2

Not the best solution but should work until you figure it out.

ALTER PROCEDURE [dbo].[Audioblog_getarchivedates] 
AS 
  BEGIN 
    SELECT DISTINCT Datename(MONTH,a.DATE) + ' ' + Datename(YEAR,a.DATE) AS archivedate,
    Cast(Datename(MONTH,a.DATE) + ' ' + Datename(YEAR,a.DATE) AS DATETIME) AS tmp
    FROM audio_blog a 
    ORDER BY tmp DESC 
  END

Answer 3

You want to order by a datetime value, but your groups don't have that. One way is to pick one of the createddates arbitrarily (e.g. the MAX) within each group, and ordewr by those:

select ArchiveDate from (
select DateName(Month,a.createddate) + ' ' + DateName(Year,a.createddate) as ArchiveDate, MAX(createddate) as createddate
from (select CONVERT(datetime,createddate) as createddate from (select '20100101' as createddate union all select '20100201' union all select '20100301') t) a 
group by DateName(Month,a.createddate) + ' ' + DateName(Year,a.createddate) ) g
order by g.createddate desc

Answer 4

order by 1 (desc)

Answer 5

Jeff Smith has written an excellent article on methods how to group and order by Year-Month pairs. I like especially his approach of rounding the date to the first day of the month and grouping by that value:

GROUP BY dateadd(month, datediff(month, 0, SomeDate),0)

It's faster because it doesn't require string concatenation.

You can convert this value to a year-month combination later on in the query by executing YEAR and MONTH on it for a user-friendly representation.