Selecting the second row of a table using rownum

Question

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:

ROWNUM  EMPNO      
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?

score 64 · Accepted Answer · edited Dec 12 '18 at 14:56

64

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 empno
from
    (
    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

edited Dec 12 '18 at 14:56

D-Shih

44,943
6
31
51

answered Feb 11 '12 at 12:31

Florin Ghita

17,525
6
57
76

:Thanks for the explanation +1 ...the query is not worth because i want the rownum of the records fetched on the basis of the order by sal desc ,the query dint work if we put rownum in the inner query ,it will gives us rownum of the records of emp table ,not of the sorted data....Thanks for the explaination – Gaurav Soni Feb 11 '12 at 12:44
Good answer (+1), but the query you proposed won't work correctly and return the 2nd highest-paid employee. You'll need another level of subquery to make sure ROWNUM is assigned _after_ ORDER BY. – Branko Dimitrijevic Feb 11 '12 at 13:15
As a minor improvement you could reduce the middle query to rownum <= 2. That will probably improve performance when the table is large. One problem here though, is we are still not finding the employee with the second highest salary. If there are top two employees have the same salary, we are essentially picking one arbitrarily... If we really want the employees with the second highest salary need to do a query that orders distinct salaries... – user6856 Feb 10 '15 at 17:00
@user6856 Oracle allways optimize this kind of query. The query proposed by me will scan the emp table only once and, while scanning, in memory will be only two rows, those with highest salary. – Florin Ghita Feb 11 '15 at 14:50
@FlorinGhita That is why oracle gets paid the big bucks. They are great at optimizations. Sometimes in fact try to optimize a query makes oracle slower because it makes the query too complicated for the automatic optimizations. – user6856 Feb 13 '15 at 16:23

score 11 · Answer 2 · answered Feb 11 '12 at 13:02

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

Pitty this becomes so much more complicated when selecting * instead if you don't want the rownum as part of the output. Especially when doing a join or querying a view. — user6856, Feb 10 '15 at 16:37

score 2 · Answer 3 · answered May 09 '17 at 09:55

2

For nth row using rownum in oracle:

select * from TEST WHERE ROWNUM<=n
MINUS
select * from TEST WHERE ROWNUM<=(n-1);

Example for second row :

select * from TEST WHERE ROWNUM<=2
MINUS
select * from TEST WHERE ROWNUM<=1;

answered May 09 '17 at 09:55

Md. Kamruzzaman

1,895
16
26

Kamruzzaman : There must be criteria to fetch the nth record ,for eg second highest salary ,there is no such logic in your query – Gaurav Soni May 09 '17 at 14:01

score 2 · Answer 4 · answered Feb 11 '12 at 12:30

2

try this:

SELECT ROW_NUMBER() OVER (ORDER BY empno) AS RowNum,
       empno
FROM   tableName
WHERE  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

answered Feb 11 '12 at 12:30

John Woo

258,903
69
498
492

:Thanks for the solution ,but i am not looking for the solution here ,i am looking for the cause why the above query is not working .. – Gaurav Soni Feb 11 '12 at 12:31
1

Believe your `where` clause should have `RowNum` instead of `RowNumber` – mahi_0707 Apr 12 '16 at 21:57

score 0 · Answer 5 · edited Oct 17 '22 at 18:40

0

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

edited Oct 17 '22 at 18:40

T.S.

18,195
11
58
78

answered Jul 07 '15 at 06:57

Deepak R

11
3

Hello Deepak, and welcome to SO! While your answer may be correct, what's the added value compared to the already accepted answer? Imho your answer is redundant and does not add any helpful information. Thus it is unlikely to receive any positive acknowledgement. Focus on unanswered questions or adding answers with additional value to gain some reputation. – cfi Jul 07 '15 at 20:04

score 0 · Answer 6 · edited Dec 08 '16 at 08:49

0

Select * From (SELECT *,
  ROW_NUMBER() OVER(ORDER BY column_name  DESC) AS mRow

FROM table_name 

WHERE condition) as TT
Where TT.mRow=2;

edited Dec 08 '16 at 08:49

FelixSFD

6,052
10
43
117

answered Dec 08 '16 at 08:33

Sachin Patil

1

Nasir Uddin · Answer 7 · 2019-12-09T17:15:19.317

0

Select Second Row From a Table in Oracle

SELECT *
FROM (SELECT * FROM emp ORDER BY rownum DESC)
WHERE rownum=1

edited Dec 09 '19 at 17:15

answered Dec 09 '19 at 17:07

Nasir Uddin

9
1

score 0 · Answer 8 · answered Oct 22 '20 at 09:51

try this way it's working 100% SQL> SELECT * FROM STUD;

RNUMBER SNAME MARKS

   104 mahesh                       85
   101 DHANU                        20
   102 BHARATH                      10
   100 RAJ                          50
   103 GOPI                         65

SQL> select * from(select MARKS,ROWNUM AS RS from ( select * from stud order by marks desc)) where RS=2;

 MARKS         RS

    65          2

SQL>

score -1 · Answer 9 · edited May 10 '16 at 13:06

-1

You can use RANK or DENSE_RANK to achieve what you are trying to achieve here.

edited May 10 '16 at 13:06

CherryDT

25,571
5
49
74

answered May 10 '16 at 12:19

ash

1

you should provide an example – Austin May 10 '16 at 12:22

score -1 · Answer 10 · edited Oct 17 '22 at 18:42

-1

Try this query 100% working

Select * from(select rownum as rn,emp.* from emp) Where rn=2;

edited Oct 17 '22 at 18:42

T.S.

18,195
11
58
78

answered Mar 29 '21 at 18:17

Pramod Tanwar

1

Selecting the second row of a table using rownum

10 Answers10

Linked

Related