www.giannistsakiris.com

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;