CODEWITHSUNDEEP
sql-server
Mr.Glaurung
Mr.Glaurung

Reputation: 596

Order by in SQL Server does not give me the same result

Why does:

ORDER BY mx.Total, o.OrderID, ol.Quantity DESC

give a completely different answer than:

ORDER BY mx.Total DESC, o.OrderID DESC, ol.Quantity DESC

It feels like I missed something in my SQL education...

Upvotes: 0

Views: 39

Answers (2)

Nguyễn Hải Triều
Nguyễn Hải Triều

Reputation: 1464

ORDER BY clause structure:

   ORDER BY expression [ASC, DESC]

ASC: min value to max value
DESC: max value to min value

If not have ASC or DESC, default ASC. And expressions|columns not shared ASC|DESC. It mean: Col1, Col2 DESC not like (Col1, Col2) DESC

When in ORDER BY have many expressions|columns, first column to first sort (order), then next column sort based on the result sorted first colum, and next...

For example:

Col1  Col2  Col3
 1     2     3
 2     3     1
 3     4     1
 2     3     4
 1     2     4
 1     4     4
 2     2     3

When: ORDER BY Col1, Col2, Col3, result will:

 Col1  Col2  Col3
   1     2     3
   1     2     4
   1     4     4
   2     2     3
   2     3     1     
   2     3     4         
   3     4     1

Upvotes: 1

juergen d
juergen d

Reputation: 204924

Because the default order is ASC if you don't specify one. So

ORDER BY mx.Total, o.OrderID, ol.Quantity DESC

is in fact

ORDER BY mx.Total ASC, o.OrderID ASC, ol.Quantity DESC

You have to specify the order for every column!

Upvotes: 3

Related Questions