ORDER BY elements should appear in the selection list if a SELECT DISTINCT error is selected

Question

I need to specify a field in a certain way, but sort it differently. Here is the request. How to get around this?

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

+4

user1345246 Mar 20 '13 at 19:39

2 answers

how to hack:

 select grp from ( select distinct tsgroup as grp from master ) order by RIGHT(RTRIM(grp), 3) , left(rtrim(grp), 3)

0

Randy Mar 20 '13 at 19:41

Martin smith · Accepted Answer · 2013-03-20T19:41:12+0000

Instead of GROUP BY you can use

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