SELECT DISTINCT with LEFT JOIN, ORDERed BY in t-SQL

Question

I have the following table in SQL Server 2008:

CREATE TABLE tbl (ID INT, dtIn DATETIME2, dtOut DATETIME2, Type INT)

INSERT tbl VALUES
(1, '05:00', '6:00', 1),
(2, '05:00', '7:00', 1),
(3, '05:01', '8:00', 1),
(4, '05:00', '8:00', 1),
(5, '05:00', '6:00', 2),
(6, '05:00', '7:00', 2)

that selects IDs of all records of the same type, with the same dtIn date, ordered by stOut in ascending order:

SELECT DISTINCT tbl.id FROM tbl   
  LEFT JOIN tbl AS t1
  ON tbl.type = t1.type AND
     tbl.dtIn = t1.dtIn
  ORDER BY tbl.dtOut ASC

But it gives me an error:

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

I tried putting that ORDER BY in different places and it all doesn't seem to work. What am I doing wrong here?

Answer 1

It doesn't make sense to ORDER by a column that is not included in the list of DISTINCT items.

Let's use a simple scenario. First, a table FruitPerson:

Fruit  PersonName
-----  ------
Apple  Joe
Apple  Mary
Peach  Karen
Peach  Lance

Here's the query equivalent to what you're trying to do:

SELECT DISTINCT Fruit
FROM FruitPerson
ORDER BY PersonName;

The result of this query without the ORDER BY is this:

Fruit
-----
Apple
Peach

But now, the question is: how should the engine order these two values "Apple" and "Peach" by PersonName? Which PersonNames? There are two people per fruit! Which name(s), exactly, should it use to decide whether Apple or Peach comes first?

Instead, rewrite your query as an aggregate:

SELECT Fruit, MinName = Min(PersonName) -- which name to order on
FROM FruitPerson
GROUP BY Fruit -- here's how you get your distinctness
ORDER BY MinName;