ORDER BY items must appear in the select list if SELECT DISTINCT is specified error

Question

I need to list a field a certain way but sort it differently. Here is the query. Any way around this?

SELECT DISTINCT tsgroup
FROM   master
ORDER  BY RIGHT(RTRIM(tsgroup), 3),
          LEFT(rtrim(tsgroup), 3) 

Answer 1

You can use GROUP BY instead

SELECT tsgroup
FROM   master
GROUP  BY tsgroup
ORDER  BY RIGHT(RTRIM(tsgroup), 3),
          LEFT(RTRIM(tsgroup), 3)