This Online Tech Support page is based on examples. There is some confusion using function Oracle ROWNUM in Oracle SQL queries and especially how to use it in the WHERE clause. This post is trying to bring some clear ideas about it and to explanation what are the up or down-sides using this functions in queries.
To explain the cases better we need some sample data. The following select statement will generate 20 lines with repeating values using Oracle MOD and ROWNUM functions.
SELECT mod (rownum,3) AS numbers FROM dual CONNECT BY rownum < 21;
The query output looks follow:
The next SQL will use the query above and it has additional condition that would limit the output only to values that has number “2” and the row limit is set to up to 3 rows. Take a look at the query below:
SELECT *
FROM (SELECT mod (rownum,3) AS numbers
FROM dual
CONNECT BY rownum < 21)
WHERE numbers = 2
AND rownum <= 3;
The third example is done using ORDER BY condition and in this example we would like to have in the output up to 4 rows with the greatest numbers and the SQL query is following:
SELECT *
FROM (SELECT mod (rownum,3) AS numbers
FROM dual
CONNECT BY rownum <= 21)
WHERE rownum <= 4
ORDER BY numbers DESC;
If you take a look at the output it isn’t what we did expect. Instead of returning four rows with value “2” the query returned all types of values. The reason is in Oracle execution mode that applies first the WHERE conditions and later comes turn to ORDER BY. This is common mistake done by developers and to get the result we are looking for we just need to bring the WHERE condition outside from the main query. See the query below there is two pair of SELECT FROM keywords and the output fills our expectations:
SELECT *
FROM (SELECT mod (rownum,3) AS numbers
FROM dual
CONNECT BY rownum <= 21
ORDER BY 1 DESC)
WHERE rownum <= 4;
To avoid the mistakes described above keep in mind the Oracle SQL execution statement and how it does work.
See Also:
Oracle Select Home