Feb 252013
select only_the_second_row,
from   queries
where  rownum = 2;

no rows selected
A complete misunderstanding of ROWNUM seems to be endemic, judging from the number of forum questions asked about it.
The key thing to remember is it is set after processing the where clause, so you can't filter it there!

For a good explanation of this (including how to fix it) see this.

  2 Responses to “The ROWNUM Contradiction”

  1. create table queries
    only_the_second_row varchar2(100),
    will_never_work varchar2(100)

    insert into queries values('since assigned after there where-clause and hence can''t be filtered there', ', we have to specify the filter in the outer query');
    insert into queries values('can be filtered like this, but it', 'when you filter it in the inner query !');

    select only_the_second_row,
    select queries.*,
    from queries
    where "ROWNUM" = 2

    ONLY_THE_SECOND_ROW : can be filtered like this, but it
    WILL_NEVER_WORK : when you filter it in the inner query !

    • I thought you might point out the resolution to this Matthias! ;)

      I didn’t realise that “ROWNUM” would be evaluated to the column name from the inner query rather than the psuedo-column though; another example of how quotes (or lack thereof) can cause different behaviour to what you expect…

 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>