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;