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

When you narrow it down to individual id's, you create the possibility that each id might have more than one dtOut associated with it. When that happens, how will Sql Server know which order to use?

You could try:

SELECT t1.id
FROM tbl t1
LEFT JOIN  tbl t2 on t1.type = t2.type AND t1.dtIn = t2.dtIn
GROUP BY t1.id, t2.dtOut
ORDER BY t2.dtOut

However, as I mentioned above this can open the possibility of having the same id listed more than once, if it matches to more than one record on the right-side table.


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;