Oracle Rownum = 2 not returning results

Question

USER_DTLS table
-----------------
ID  NAME    ADDRESS ROLL

1   SARAH   (Clob)  14
2   ALICE   (Clob)  13
3   ANU (Clob)  12

on this table

SELECT * FROM test.USER_DTLS
 WHERE ROWNUM =1;

return result

1   SARAH   (Clob)  14

but while trying

SELECT * FROM test.USER_DTLS
 WHERE ROWNUM =2;

It return no result can anybody please help this

Why have you tagged this question with MySQL? There is no such thing like ROWNUM in MySQL. — Ravi., Jun 07 '14 at 06:40
This is documented in the manual: http://docs.oracle.com/cd/E11882_01/server.112/e26088/pseudocolumns009.htm#SQLRF00255 — , Jun 07 '14 at 06:41
@Ravi.Sorry there is no tags related to that present also it asked 5 tags that is the reason i include that. — Vinoth Kumar, Jun 07 '14 at 06:50
@a_horse_with_no_name documentation is ok but the query is not working any idea about that. — Vinoth Kumar, Jun 07 '14 at 06:51

score 7 · Answer 1 · answered Jun 07 '14 at 06:56

Oracle's ROWNUM starts on 1 and is only incremented when assigned to a row that passes the WHERE condition.

Since you're filtering on ROWNUM=2, ROWNUM=1 doesn't pass the WHERE condition, and ROWNUM is therefore never assigned to a row and incremented to 2.

This ask Tom article goes into detail on this, and is very good reading if you want to use ROWNUM efficiently.

1 Answers1