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

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?

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)

You can use GROUP BY instead of DISTINCT like this

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

With order by on ReportDate desc, using row_number() instead of group by.

select substring(convert(varchar, Env.ReportDate, 100),1,3) +'-'+
          substring(convert(varchar, Env.ReportDate, 100),8,4 )
from (select
        ReportDate,
        row_number() over(partition by datepart(year, ReportDate), datepart(month, ReportDate)
                          order by (select 1)) as rn
      from [EnvelopsDB].[dbo].[Envelopes]) as Env    
where Env.rn = 1
order by Env.ReportDate desc

I think there was a similar question here recently, which I can't find now, but the answer was something to this effect:

SELECT
  SUBSTRING(CONVERT(varchar, ReportDate, 100), 1, 3) + '-' +
  SUBSTRING(CONVERT(varchar, ReportDate, 100), 8, 4)
FROM [EnvelopsDB].[dbo].[Envelopes]
GROUP BY
  SUBSTRING(CONVERT(varchar, ReportDate, 100), 1, 3),
  SUBSTRING(CONVERT(varchar, ReportDate, 100), 8, 4)
ORDER BY MIN(ReportDate)

Also, while the way you've chosen to present the output in the format of mmm-yyyy is basically fine, I would probably do the same slightly differently. Here:

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

If you do not mind an extra column in the result set, then this will work.

SELECT DISTINCT
    REPLACE(RIGHT(CONVERT(VARCHAR(11), ReportDate, 106), 8), ' ', '-') AS [Mon-YYYY],
    RANK() OVER(ORDER BY CONVERT(VARCHAR(7), ReportDate, 120) /* [YYYY-MM]*/ DESC) AS r_order
FROM [EnvelopsDB].[dbo].[Envelopes]
ORDER BY r_order DESC