Select Nth Row From A Table In Oracle
Will not works with '=' (will works <2 or >2, but not equal)
so you can
SELECT Price from (SELECT PRICE, ROWNUM AS RN FROM AAA_PRICING) WHERE RN = 2
Based on the classic answer:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:127412348064
select *
from ( select a.*, rownum rnum
from ( YOUR_QUERY_GOES_HERE -- including the order by ) a
where rownum <= N_ROWS )
where rnum >= N_ROWS
/
To address the reason for this:
The RowNum
is a pseudo-column supplied by Oracle. It is generated while the SELECT-clause is being processed. Since the WHERE-clause is handled before the SELECT-clause, the RowNum
does not have a proper value yet.
One can argue whether or not it makes sense to have Oracle throw an exception in situation, but because RowNum
still is a pseudo-column it's still valid to have it there.
Note: Don't confuse this with RowId
, which is an entire different story!
IMPORTANT EDIT:
Note that what I wrote about RowNum
is only true for =
, >
, >=
, IN ()
and maybe others. If you check for, e.g. RowNum < 10
, you only get nine records!? I don't know why that is the case!