Selecting the second row of a table using rownum

To explain this behaviour, we need to understand how Oracle processes ROWNUM. When assigning ROWNUM to a row, Oracle starts at 1 and only increments the value when a row is selected; that is, when all conditions in the WHERE clause are met. Since our condition requires that ROWNUM is greater than 2, no rows are selected and ROWNUM is never incremented beyond 1.

The bottom line is that conditions such as the following will work as expected.

.. WHERE rownum = 1;

.. WHERE rownum <= 10;

While queries with these conditions will always return zero rows.

.. WHERE rownum = 2;

.. WHERE rownum > 10;

Quoted from Understanding Oracle rownum

You should modify you query in this way in order to work:

select empnofrom    (    select empno, rownum as rn     from (          select empno          from emp          order by sal desc          )    )where rn=2;

EDIT: I've corrected the query to get the rownum after the order by sal desc

In the first query, the first row will have ROWNUM = 1 so will be rejected. The second row will also have ROWNUM = 1 (because the row before was rejected) and also be rejected, the third row will also have ROWNUM = 1 (because all rows before it were rejected) and also be rejected etc... The net result is that all rows are rejected.

The second query should not return the result you got. It should correctly assign ROWNUM after ORDER BY.

As a consequence of all this, you need to use not 2 but 3 levels of subqueries, like this:

SELECT EMPNO, SAL FROM ( -- Make sure row is not rejected before next ROWNUM can be assigned.    SELECT EMPNO, SAL, ROWNUM R FROM ( -- Make sure ROWNUM is assigned after ORDER BY.        SELECT EMPNO, SAL        FROM EMP        ORDER BY SAL DESC    ))WHERE R = 2

The result:

EMPNO                  SAL                    ---------------------- ---------------------- 3                      7813                   

try this:

SELECT ROW_NUMBER() OVER (ORDER BY empno) AS RowNum,       empnoFROM   tableNameWHERE  RowNumber = 2;

Snippet From Source:

SELECT last_name FROM       (SELECT last_name, ROW_NUMBER() OVER (ORDER BY last_name) R FROM employees)WHERE R BETWEEN 51 and 100

REFERENCE