How does order by work if two values ​​are equal?

Question

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 request

 select feature,section,page from NEWSPAPER where section = 'F' order by page; 

He gives this conclusion

 Doctor Is In F 6 Obituaries F 6 Births F 7 Classified F 8 

But in Kevin Loney Oracle 10g Complete Reference, the output is as follows

 Obituaries F 6 Doctor Is In F 6 Births F 7 Classified F 8 

Please help me understand how this happens?

Answer 1

In relational databases, tables are sets and are disordered. The order by clause is used primarily for output purposes (and several other cases, such as a subquery containing rownum ).

This is a good place to start. The SQL standard does not specify what should happen when the keys on order by match. And this is for a good reason. You can use various methods to sort. Some may be stable (keeping the original order). Some methods may be different.

Focus on whether the same lines are in the sets, not their order. By the way, I consider this a bad example. In its examples, the book should not have ambiguous roles.

Answer 2

If you need reliable, reproducible ordering when the two values ​​in the first column of the ORDER BY match, you should always specify another, additional column, which should also be included. Although you can assume that they will sort themselves based on the entered order (almost always, as far as I know, but remember that the SQL standard does not specify any form by default) or index, you should never (unless it specially documented as such for the engine you are using - and even then I personally will never rely on it).

Your request, if you want to sort alphabetically by characteristics on each page, should be:

 select feature,section,page from NEWSPAPER where section = 'F' order by page, feature;