CODEWITHSUNDEEP
sqloracle-databaseoracle10grownum
Gaurav Soni
Gaurav Soni

Reputation: 6338

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:

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?

Upvotes: 28

Views: 88432

Answers (10)

Pramod Tanwar
Pramod Tanwar

Reputation: 1

Try this query 100% working

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

Upvotes: -1

Deepak R
Deepak R

Reputation: 11

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

Upvotes: 0

Deshaboina Raju
Deshaboina Raju

Reputation: 1

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>

Upvotes: 0

Nasir Uddin
Nasir Uddin

Reputation: 9

Select Second Row From a Table in Oracle

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

Upvotes: 0

Florin Ghita
Florin Ghita

Reputation: 17643

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

Upvotes: 64

Md. Kamruzzaman
Md. Kamruzzaman

Reputation: 1905

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;

Upvotes: 2

Sachin Patil
Sachin Patil

Reputation: 1

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

FROM table_name 

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

Upvotes: 0

ash
ash

Reputation: 1

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

Upvotes: -1

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

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

Upvotes: 11

John Woo
John Woo

Reputation: 263723

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

Upvotes: 2

Related Questions