Systems Engineering and RDBMS

  • Click Here for Decipher's Homepage

  • Categories

  • Questions?

    Please send your wish list of things that you would like us to write about or if you have suggestions to help improve this blog site. You can send all questions/suggestions to: Blog Support
  • Archives

  • Blog Stats


Archive for May 31st, 2007

Two common ROWNUM mistakes

Posted by decipherinfosys on May 31, 2007

As you know, ROWNUM is a pseudo-column that Oracle exposes for a given result set. This value gets assigned to the result set as they satisfy the predicate (the where clause). Two common mistakes that beginners make when working with rownum are:

1) Using an equality operator for the ROWNUM clause.
2) Using ROWNUM filter prior to applying the ORDER BY clause.

#1 above means that conditions like:

…where rownum = 2


…where rownum > 10

will never ever return results. Why is that? That is because the value for the pseudo-column gets assigned ONLY after the rows are selected as an output. That is why if you want to select say 20 random rows (without the sort), you can do:

…where rownum < 21

#2 means that if you have a query like:

select * from Emp_Master where rownum < 21 order by salary desc

And if you are expecting that you will get the top 20 highest paid employees, that is not the result that you will be getting. The reason being that in this case, the rownum is getting applied before the sort happens. If you want to get the top 20 highest paid employees, you need to first sort the result before putting the filter:

select * from (select * from Emp_Master order by salary desc) where rownum < 21

We had covered this as well as how to select for different ranges in our Paging record-sets whitepaper which you can download from our site here.

Posted in Oracle | Leave a Comment »