How To Use Oracle ROWNUM Function
  Home
 

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:

online tech support computer help computer technician computer problems oracle rownum oracle rownum in oracle database software database oracle sql database sql oracle retirement planning retirement

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;

online tech support computer help computer technician computer problems oracle rownum oracle rownum in oracle database software database oracle sql database sql oracle retirement planning retirement

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;

online tech support computer help computer technician computer problems oracle rownum oracle rownum in oracle database software database oracle sql database sql oracle retirement planning retirement

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;

online tech support computer help computer technician computer problems oracle rownum oracle rownum in oracle database software database oracle sql database sql oracle retirement planning retirement

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



See Also:
Oracle Select Home

 Leave a Reply

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=""> <s> <strike> <strong>

(required)

(required)

*