Cannot use ORDER BY with DISTINCT and CONVERT

[aladiver]

The following SQL code works fine if I don't use the CONVERT to make my column two characters wide. When using it as follows the error message reads:
"ORDER BY items must appear in the select list if SELECT DISTINCT is specified." If I just use:
SELECT DISTINCT MONTH(CTNoteDate) it works fine.

How can I run this query with the column width set and sort the results?

SELECT DISTINCT CONVERT(varchar(2),MONTH(CTNoteDate)) MO,
                DATENAME(month,CTNoteDate) MONTH 
FROM CTNotes WHERE YEAR(CTNoteDate)=2014
ORDER BY MONTH(CTNoteDate)

Thanks! ~GregM

[Rabbit]

Please use code tags when posting code or formatted data.

If you're going to use an ORDER BY with a DISTINCT, whatever is in the ORDER BY must also be in the SELECT.

Therefore you have 2 solutions, include what's in your ORDER BY in your SELECT:

SELECT DISTINCT CONVERT(varchar(2),MONTH(CTNoteDate)) MO,
                DATENAME(month,CTNoteDate) MONTH,
                MONTH(CTNoteDate)
FROM CTNotes WHERE YEAR(CTNoteDate)=2014
ORDER BY MONTH(CTNoteDate)

Or change your ORDER BY to match the expression in the SELECT:

SELECT DISTINCT CONVERT(varchar(2),MONTH(CTNoteDate)) MO,
                DATENAME(month,CTNoteDate) MONTH 
FROM CTNotes WHERE YEAR(CTNoteDate)=2014
ORDER BY CONVERT(varchar(2),MONTH(CTNoteDate))

[aladiver]

Very sorry about forgetting the code tags.

Thank you very much -- it makes all the sense in the world, I just couldn't figure out HOW to include the select code in the order.

Have a great day!

[Rabbit]

No problem, good luck with the rest of your project!