How to Use Rownum In Oracle

How to Use Rownum In Oracle

July 07, 2020
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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:

database management system oracle performance tuning oracle rownum oracle rownum in oracle dba oracle online courses online personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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;

database management system oracle performance tuning oracle rownum oracle rownum in oracle dba oracle online courses online personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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;

database management system oracle performance tuning oracle rownum oracle rownum in oracle dba oracle online courses online personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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;

database management system oracle performance tuning oracle rownum oracle rownum in oracle dba oracle online courses online personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

To avoid the mistakes described above keep in mind the Oracle SQL execution statement and how does it work.

See Also:
Oracle Select Home

Leave a Reply

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

*