Order by clause with distinct

Question

I have a table in sql server in which I have some values. For example as below,

StationId
-----
3
11
4

I have written a query as below,

select  STUFF((SELECT distinct (',' + QuoteName(c.StationId))
            FROM Analytics c 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'') 

where output as below:

[11],[3],[4]

Now my requirement is I need the output as [3],[4],[11]. So when rewrite the query as

select  STUFF((SELECT distinct (',' + QuoteName(c.StationId))
            FROM Analytics c ORDER BY c.StationId 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

I am getting below exception

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

Please give me some suggestions.

Answer 1

You can alter your code by removing the DISTINCT and adding a GROUP BY to use with the ORDER BY:

select  STUFF((SELECT (',' + QuoteName(c.StationId))
            FROM Analytics c 
            group by c.StationId
            ORDER BY c.StationId 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

See SQL Fiddle with Demo