Geek asks and answers

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

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)

which selects the identifiers 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 this gives me an error:

ORDER BY elements should appear in the selection list if SELECT DISTINCT is specified

I tried to put this ORDER BY in different places, and all this does not seem to work. What am I doing wrong here?

+5
source share
5 answers

When you shorten individual identifiers, you create the possibility that each identifier can have more than one dtOut associated with it. When this happens, how does Sql Server know which order to use?

You can 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

, , , , .

+3

ORDER , DISTINCT.

. -, FruitPerson:

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

, :

SELECT DISTINCT Fruit
FROM FruitPerson
ORDER BY PersonName;

ORDER BY :

Fruit
-----
Apple
Peach

: "Apple" "Peach" PersonName? ? ! (), , , , Apple Peach?

:

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

ORDER BY, select, DISTINCT. , tbl.id

, DISTINCT , . , , tbl.dtOut, DISTINCT, tbl.id tbl.dtOut tbl.id, , , .

, .

0

tbl.dtOut , , .

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

You had this error because you did not put (tbl.dtOut) in the select list.

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

You will get a result set (having 3 rows) and if you want the id field as well then

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

(with 6 lines, as it gives a separate combination from the id / date field)

0
source

More articles:


All Articles