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

sql distinct for-xml-path
mdutra picture mdutra · Jan 28, 2014 · Viewed 7.3k times · Source

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?

Answer

soysal picture soysal · Jan 28, 2014

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

Related questions

How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?

My table is: id home datetime player resource ---|-----|------------|--------|--------- 1 | 10 | 04/03/2009 | john | 399 2 | 11 | 04/03/2009 | juliet | 244 5 | 12 | 04/03/2009 | borat | 555 3 | 10 | 03/03/2009 | john | 300 4 | 11 | 03/03/2009 | juliet | 200 6 | 12 | 03/03/2009 | borat | 500 7 | 13 | 24/12/2008 | borat | 600 8 | 13 | 01/01/2009 | borat | 700 I need to select each distinct home holding the maximum value of datetime. Result would be: id home datetime …

Read More
How do I (or can I) SELECT DISTINCT on multiple columns?

I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales …

Read More
SQL/mysql - Select distinct/UNIQUE but return all columns?

SELECT DISTINCT field1, field2, field3, ...... FROM table I am trying to accomplish the following sql statement but I want it to return all columns is this possible? Something like: SELECT DISTINCT field1, * from table

Read More