Gaurav Soni Gaurav Soni - 2 years ago 98
SQL Question

Selecting the second row of a table using rownum

I have tried the below query:

select empno from (
select empno
from emp
order by sal desc
where rownum = 2

This is not returning any records.

When I tried this query

select rownum,empno from (
select empno from emp order by sal desc)

It gives me this output:

1 7802
2 7809
3 7813
4 7823

Can anyone tell me what's the problem with my first query? Why is it not returning any records when I add the ROWNUM filter?

Answer Source

To explain this behaviour, we need to understand how Oracle processes ROWNUM. When assigning ROWNUM to a row, Oracle starts at 1 and only 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 empno
    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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download