Feb 252013
 
select only_the_second_row,
       will_never_work
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.

If you enjoyed this article, Get email updates (It’s Free)

  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 !');

    begin
    print_table(q'|
    select only_the_second_row,
    will_never_work
    from
    (
    select queries.*,
    rownum
    from queries
    )
    where "ROWNUM" = 2
    |');
    end;
    /

    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

(required)

(required)

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