Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How does order by clause works if two values are equal?

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?

like image 801
Abhishek kumar Avatar asked Dec 27 '12 16:12

Abhishek kumar


People also ask

Can we use ORDER BY for 2 columns?

Discussion: If you want to select records from a table but would like to see them sorted according to two columns, you can do so with ORDER BY . This clause comes at the end of your SQL query.

How does ORDER BY works on multiple columns?

If you specify multiple columns, the result set is sorted by the first column and then that sorted result set is sorted by the second column, and so on. The columns that appear in the ORDER BY clause must correspond to either column in the select list or columns defined in the table specified in the FROM clause.

How does MySQL order rows with the same value?

The order that rows are returned in is guaranteed ONLY by ORDER BY clause (or in MySQL, an ORDER BY implicitly specified in the GROUP BY clause.) Apart from that, there is NO GUARANTEE of the order rows will be returned in. Apart from that, MySQL is free to return the rows in any sequence.

What does ORDER BY 2 mean in SQL?

It will order the results by the first column, and then, if there are some rows with the same value in the first column it will order them by the second column.


2 Answers

If you need reliable, reproducible ordering to occur when two values in your ORDER BY clause's first column are the same, you should always provide another, secondary column to also order on. While you might be able to assume that they will sort themselves based on order entered (almost always the case to my knowledge, but be aware that the SQL standard does not specify any form of default ordering) or index, you never should (unless it is specifically documented as such for the engine you are using--and even then I'd personally never rely on that).

Your query, if you wanted alphabetical sorting by feature within each page, should be:

SELECT feature,section,page FROM NEWSPAPER
WHERE section = 'F'
ORDER BY page, feature;
like image 192
taswyn Avatar answered Sep 18 '22 21:09

taswyn


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.

like image 38
Gordon Linoff Avatar answered Sep 22 '22 21:09

Gordon Linoff