This is my NEWSPAPER table.
National News A 1 Sports D 1 Editorials A 12 Business E 1 Weather C 2 Television B 7 Births F 7 Classified F 8 Modern Life B 1 Comics C 4 Movies B 4 Bridge B 2 Obituaries F 6 Doctor Is In F 6
When i run this query
select feature,section,page from NEWSPAPER where section = 'F' order by page;
It gives this output
Doctor Is In F 6 Obituaries F 6 Births F 7 Classified F 8
But in Kevin Loney’s Oracle 10g Complete Reference the output is like this
Obituaries F 6 Doctor Is In F 6 Births F 7 Classified F 8
Please help me understand how is it happening?
Advertisement
Answer
In relational databases, tables are sets and are unordered. The order by
clause is used primarily for output purposes (and a few other cases such as a subquery containing rownum
).
This is a good place to start. The SQL standard does not specify what has to happen when the keys on an order by
are the same. And this is for good reason. Different techniques can be used for sorting. Some might be stable (preserving original order). Some methods might not be.
Focus on whether the same rows are in the sets, not their ordering. By the way, I would consider this an unfortunate example. The book should not have ambiguous sorts in its examples.