Oracle: Select top rows from an ordered result set

Oracle provides the ROWNUM pseudo-column to allow someone limit up the number of returned rows in a SELECT statement. However, you should be cautious when using ROWNUM in combination with an ORDER BY clause.

For example, consider the following table:

ColumnName
30
40
50
60
70
80
90

We can easily select the first three (unordered) rows, like this:

SELECT * FROM TableName WHERE ROWNUM <= 3;

ColumnName
30
40
50

Or we can even select the entire data set in descending order, like this:

SELECT * FROM TableName ORDER BY ColumnName DESC;

ColumnName
90
80
70
60
50
40
30

But, how about selecting the three highest values? A common pitfall is trying something like this one:

SELECT * FROM TableName WHERE ROWNUM <= 3 ORDER BY ColumnName DESC;

But this will falsely and not surprisingly return this result this:

ColumnName
50
40
30

What did go wrong? The answer is simple. At first, Oracle select the first three rows from the table in their natural order of occurance. They happen to be 30, 40 and 50. Then, Oracle sorts up the result set and returns it.

Intuitively, someone would think that the sorting and the row limit should occur in the reverse order, and that’s correct. This is the proper way to do it:

SELECT * FROM (SELECT * FROM TableName ORDER BY ColumnName) WHERE ROWNUM <= 3;

ColumnName
90
80
70

In general, when you have an ordered result set, and you want to select only the first N rows of it, this is the syntax you should use:

SELECT * FROM (SELECT * FROM … ORDER BY …) WHERE ROWNUM <= N;

This entry was posted in Uncategorized. Bookmark the permalink.

8 Responses to Oracle: Select top rows from an ordered result set

  1. william says:

    Oracle creating a new idea , i like it i will apply this in my next braindumps project table. i will write you again

  2. jp says:

    You can also use the TOP N clause, which works on Oracle and most other databases e.g.:

    select top 3 * from … order by …

    On Oracle & MS SQL you can even select the top N%, e.g.:

    select top 25 percent * from … order by …

  3. CM says:

    jp is talking nonsense – in Oracle there is no top command

  4. ... says:

    Agreed, total nonsense, why would someone want to leave a false advice/instructions and waste their and other people’s time..

  5. Art says:

    What ca i do if i have this situation :

    Select id, (SELECT code FROM (SELECT * FROM TableName
    WHERE id = o2.id and date > o1.date
    ORDER BY ColumnName) WHERE ROWNUM = 1)
    FROM outerTable o
    LEFT JOIN otherOterTable o2 ON o2.ref_id = o.id;

    Help :)

  6. Raj says:

    Thanks mate, that worked like a charm , Thumbs Up!

  7. Harish Kumar Tarala says:

    Thanks helped me in solving my requirement.

  8. Rafance says:

    Great! It works fine!

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>