How to Order BY without having Column in SELECT

sql, sql-server

Solution

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.

Problem

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

Original source