CODEWITHSUNDEEP
sqloracle10gsql-order-by
Abhishek kumar
Abhishek kumar

Reputation: 2676

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?

Upvotes: 22

Views: 29130

Answers (3)

taswyn
taswyn

Reputation: 4513

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;

Upvotes: 15

Pratik Nabriya
Pratik Nabriya

Reputation: 123

When you use the SELECT statement to query data from a table, the order which rows appear in the result set may not be what you expected.

In some cases, the rows that appear in the result set are in the order that they are stored in the table physically. However, in case the query optimizer uses an index to process the query, the rows will appear as they are stored in the index key order. For this reason, the order of rows in the result set is undetermined or unpredictable.

The query optimizer is a built-in software component in the database system that determines the most efficient way for an SQL statement to query the requested data.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269843

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.

Upvotes: 14

Related Questions