How to SELECT DISTINCT with an ORDER BY and FOR XML in SQL Server 2008+

distinct, for-xml-path, sql

Solution

Try that;

SELECT STUFF ((
  SELECT cast('/' as Varchar(max)) + Location
     From Locations
     Group by Location
     Order by MAX(Time) FOR XML PATH('')) , 1 , 1 , '' ) as result

Problem

So I have been fighting with this for a while now and I thought this had to be a simple task. My goal is to return a single string of all the unique rows returned separated with a forward slash and ordered by the time they were entered. Here is example data Table: Locations ``` Location Time ======== ======= OR1 2013-02-06 16:55:47.000 OR1 2013-02-06 16:56:34.000 ICU1 2013-02-06 16:59:50.000 OR1 2013-02-06 17:02:50.000 ICU1 2013-02-06 17:09:50.000 ``` So given the above table of data I want to return a string that says this "OR1/ICU1". I can return the distinct values as a string using FOR XML PATH but as soon as I throw in the ORDER BY it all falls apart with errors. Ideas?

Original source