CODEWITHSUNDEEP
sqlt-sql
John
John

Reputation: 1522

TSQL, Ordering of ORDER BY

Given a table of 

Name   Age
Bob    30
Paul   35
John   35

and a statement

select * from table ORDER BY Age DESC

What is the ordering of the resulting table?

Name   Age
Paul   35
John   35
Bob    30

or 

Name   Age
John   35
Paul   35
Bob    30

or undefined behaviour?

Upvotes: 2

Views: 144

Answers (4)

zod
zod

Reputation: 12417

select * from table 
ORDER BY Age DESC , name asc

Upvotes: 0

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55489

The result finally is not in any defined or garuanteed order in which you can expect results.

Upvotes: 0

Brad
Brad

Reputation: 15577

ORDER BY Age DESC, Name [ASC|DESC]

If you omit the ORDER BY clause, the optimizer will decide the order for you. In my experience, it is usually however the data is ordered on the disk. If there is a clustered index (usually the primary key), it will be its order. However, as @Martin pointed out, this is not guaranteed. If you want a specific order, specify it.

Upvotes: 2

LukeH
LukeH

Reputation: 269368

It's guaranteed that Bob will be the last row of the resultset. The relative ordering of the other rows isn't guaranteed in any way.

If you need guaranteed ordering then you need to be explicit about it. For example:

SELECT *
FROM table
ORDER BY age DESC,
         name ASC

Upvotes: 7

Related Questions