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?
views:
51answers:
4
+2
A:
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.
Brad
2010-10-18 14:47:15
This is not defined or guaranteed.
Martin Smith
2010-10-18 14:48:50
@Martin, you are correct. I have modified my answer to reflect your input.
Brad
2010-10-18 14:55:33
If you post code or XML, **please** highlight those lines in the text editor and click on the "code" button (101 010) on the editor toolbar to nicely format and syntax highlight it!
marc_s
2010-10-18 15:07:30
That doesn't answer the question - if you do not specify the "name" as an ORDER BY criteria - is the order somehow defined - or not??
marc_s
2010-10-18 15:07:52
A:
The result finally is not in any defined or garuanteed order in which you can expect results.
Sachin Shanbhag
2010-10-18 14:48:09
+7
A:
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
LukeH
2010-10-18 14:50:05