CODEWITHSUNDEEP
sqlsql-server
Michael Rowley
Michael Rowley

Reputation: 667

How to Order BY without having Column in SELECT

I am using MS-SQL and I have a table like this:

Count  Code
1      A
2      C
3      A
4      B
5      C
6      B

I need to only to get Distinct Row of the Code Column but I need to keep the Order of the Column to create a Comma Delimited String like this:

    A, C, B

I have tried the following code to not get an SQL Error of Msg 145, Level 15 - ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

SELECT @tlist = ISNULL(@tlist+', ','') + Code 
FROM (SELECT DISTINCT t.Code  
     FROM @Table t)

but I get an output of A, B, C

Upvotes: 4

Views: 11601

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270703

So, you don't want distinct. You want group by:

select t.code
from @Table t
group by t.code
order by min(count)

You can order by columns not mentioned in the select clause.

To get the comma delimited list as a single variable, use this technique:

select stuff((select ', '+t.code
              from @Table t
              group by t.code
              order by min(count)
              for xml path ('')
             ), 1, 2, '')

Order by in a subquery is generally undefined. And, your method of concatenating the list together in order is not guaranteed to work.

Upvotes: 4

John Woo
John Woo

Reputation: 263843

order them in your subquery

SELECT DISTINCT t.Code  
FROM @Table t
ORDER BY t.Code

Upvotes: 3

Related Questions