This tutorial page is based on examples to make it easier to follow. There is a confusion using function Oracle ROWNUM and now we are trying to clarify it a bit and to bring some clear ideas how to use Rownum in Oracle Select statement. Also we are going to explain why it is good or bad to use the Rownum function in queries.
First lets see a simple Oracle Select query with Oracle Rownum. We are using Rownum in Connect By function that does selfjoin. Since the selfjoin will work in a loop because Rownum function is generating a new value dynamically every time it finds a new line, so we need to limit the amount of lines. We are going to limit to 20 as the statement show less than 21.
SELECT mod (rownum,3) AS numbers FROM dual CONNECT BY rownum < 21;
The Rownum Oracle result looks following:
The next task would be to use the query above and tailor it, so it would return only values “2” and limit it up to 3 lines. The Rownum in Oracle example looks following:
SELECT * FROM (SELECT mod (rownum,3) AS numbers FROM dual CONNECT BY rownum < 21) WHERE numbers = 2 AND rownum <= 3;
Please note that the Oracle Rownum function to limit lines up to 3 is applied in this SQL after the “main” condition called as inner-query.
The next Oracle DBA example is with additional Oracle ORDER BY condition. For example we would like to have only in the output the greatest Numbers value and the Oracle query would be limited up to 4 lines. Lets try this query:
SELECT * FROM (SELECT mod (rownum,3) AS numbers FROM dual CONNECT BY rownum <= 21) WHERE rownum <= 4 ORDER BY numbers DESC;
As you see the output isn’t what did we expect. If you take a look at the 1st query we have seven lines with value 2 and this query didn’t return four lines with greatest value “2” and instead it did return all types of values. The reason is in Oracle SQL Execution plan that runs first the WHERE conditions and later on applies ordering. This way written query and expecting different type of output is quite common mistake since most people are thinking that if you add into the WHERE condition the Rownum function and ORDER BY then it should do Order By first and then limit the lines to 4. Unfortunately Oracle takes first available 4 lines and does ordering between them.
To get the output we are looking for we need to do in the Oracle query the ORDER BY before applying line number limit as in the follow SQL:
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 does it work.