SQL Server: select distinct mon-yyyy format output sorty by descending order -


i have datetime column in table following data:

2011-03-23 2011-04-19 2011-04-26 2011-05-26 

i want select distinct mon-yyyy format output ordered report date descending. need select 1 column in sql statement

this sql works, want order reportdate column

select  distinct substring (convert(varchar, reportdate, 100),1,3) +'-'+         substring (convert(varchar, reportdate, 100),8,4 )    [envelopsdb].[dbo].[envelopes] 

output

apr-2011 mar-2011 may-2011 

this sql gives error:

select  distinct substring (convert(varchar, reportdate, 100),1,3) +'-'+         substring (convert(varchar, reportdate, 100),8,4 )    [envelopsdb].[dbo].[envelopes]   order reportdate 

error:

msg 145, level 15, state 1, line 2
order items must appear in select list if select distinct specified.

what best sql query output need?

with testdata (   select cast('2011-03-23' datetime) d union   select cast('2011-04-19' datetime) union   select cast('2011-04-26' datetime) union   select cast('2011-05-26' datetime) ) select datename(month,d)+'-'+datename(year,d) testdata group datepart(year,d), datepart(month,d), datename(month,d),datename(year,d) order datepart(year,d), datepart(month,d) 

select datename(month,reportdate)+'-'+datename(year,reportdate) [envelopsdb].[dbo].[envelopes] group datepart(year,reportdate), datepart(month,reportdate), datename(month,reportdate),datename(year,reportdate) order datepart(year,reportdate), datepart(month,reportdate) 

Comments