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

Question

I have datetime column in a table with following data:

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

I want to select distinct mon-yyyy format output ordered by report date descending. We need to select only one column in the SQL statement

This SQL works, but I want to order by ReportDate column

SELECT  distinct SUBSTRING (convert(varchar, ReportDate, 100),1,3) +'-'+
        SUBSTRING (convert(varchar, ReportDate, 100),8,4 ) 
  FROM [EnvelopsDB].[dbo].[Envelopes]

output

Apr-2011
Mar-2011
May-2011

This SQL gives an error:

SELECT  distinct SUBSTRING (convert(varchar, ReportDate, 100),1,3) +'-'+
        SUBSTRING (convert(varchar, ReportDate, 100),8,4 ) 
  FROM [EnvelopsDB].[dbo].[Envelopes]
  order by ReportDate

Error:

Msg 145, Level 15, State 1, Line 2
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

What is the best SQL query to get the output I need?

Answer 1

with testdata as
(
  select cast('2011-03-23' as datetime) as d
union all
  select cast('2011-04-19' as datetime)
union all
  select cast('2011-04-26' as datetime)
union all
  select cast('2011-05-26' as datetime)
)
SELECT DATENAME(month,d)+'-'+DATENAME(year,d)
FROM testdata
GROUP BY DATEPART(year,d), DATEPART(month,d), DATENAME(month,d),DATENAME(year,d)
ORDER BY DATEPART(year,d), DATEPART(month,d)

---

SELECT DATENAME(month,ReportDate)+'-'+DATENAME(year,ReportDate)
FROM [EnvelopsDB].[dbo].[Envelopes]
GROUP BY DATEPART(year,ReportDate), DATEPART(month,ReportDate), DATENAME(month,ReportDate),DATENAME(year,ReportDate)
ORDER BY DATEPART(year,ReportDate), DATEPART(month,ReportDate)