Ordering Month / Year Pairs in a T-SQL Query

Question

I am writing a stored procedure to display the month and year. It works, but does not return the rows in the correct 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

The results will look like this:

March 2010

January 2010
February 2010

But it is not in order (desc).

Answer 1

Not the best solution, but should work until you find 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 2

Jeff Smith wrote about grouping methods and order by month-to-month basis. I especially like his approximation of rounding the date to the first day of the month and grouping by this value:

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

, .

- , YEAR MONTH .

Answer 3

datetime, . , (, MAX) ordewr :

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

. DisplyName, SortIndex. , .

select a,b,c DisplayName   from
(select a, b,c, DisplayName, SortIndex....

) as Temp
order by Temp.SortIndex

, display/index

Answer 5

:

order by a.createddate desc

Answer 6

1 (desc)