CODEWITHSUNDEEP
sqloracle-database
Niraj Choubey
Niraj Choubey

Reputation: 4040

Select Nth Row From A Table In Oracle

How can I select the Nth row from a table in Oracle?

I tried

SELECT PRICE FROM AAA_PRICING WHERE ROWNUM = 2

but that didn't work. Please help!

Upvotes: 17

Views: 107099

Answers (14)

gyousefi
gyousefi

Reputation: 351

Try following query:

SELECT * FROM AAA_PRICING where rownum < n + 1 MINUS SELECT * FROM AAA_PRICING where rownum < n;

Upvotes: 0

Ruthwik_
Ruthwik_

Reputation: 31

You can do it by using OFFSET AND FETCH clauses.

OFFSET will skip first n rows in a result and FETCH will limit the output to n rows

So if you want to get only the 2nd row of your result, you can filter using:

SELECT * FROM TABLE_NAME
OFFSET 1 ROWS
FETCH NEXT 1 ROWS ONLY

Also keep in mind that you need to use ORDER BY clause if your result is not sorted, else results might not be as expected.

** specifically for Oracle SQL

Upvotes: 1

Ritunjay kumar
Ritunjay kumar

Reputation: 477

select a.*, rownum rnum
           from ( select * from xyz_menu order by priority desc) a
          where rownum < 5 ;

select * from xyz_menu order by priority desc

creating virtual table and also defining row number in virtual table

note: for oracle

Upvotes: 0

Singh Kailash
Singh Kailash

Reputation: 674

select * 
From (select PRICE, DENSE_RANK() over(ORDER BY PRICE desc) as RNO
      From AAA_PRICING
     ) t where RNO=2;

Upvotes: 0

sideyn
sideyn

Reputation: 21

If you are on Oracle 12 or above, You can use the result offset and fetch clauses:

SELECT PRICE FROM AAA_PRICING 
offset 1 rows fetch next 1 rows only

Upvotes: 2

Sudeep Mohan Nayak
Sudeep Mohan Nayak

Reputation: 11

ROWNUM is a pseudo column which generates unique pseudo values (equals to the number of records present in the SELECT statement o/p) during the execution of SELECT clause. When this pseudo column is specified with the WHERE clause it's value becomes 1 by default. So it behaves according to the comparison operator specified with it.

SELECT * FROM (
           SELECT ROWNUM RN, E.*
           FROM Emp E
          )
WHERE RN = 10;

Upvotes: 1

sunny
sunny

Reputation: 145

select * from (Select Price, rownum as rn from(Select * from AAA_PRICING a order by a.Price))
where rn=2;

It will give you 2nd lowest price from the Price column. If you want simply 2nd row remove Order By condition.

Upvotes: 0

Rakesh Anand
Rakesh Anand

Reputation: 423

SELECT * FROM
(SELECT PRICE, ROWNUM AS RN FROM AAA_PRICING )
WHERE RN = 2;

Upvotes: 0

Art
Art

Reputation: 31

Select * From
(
    Select Row_Number() OVER (Order by empno) rno, e.* 
    From scott.emp e
)
Where rno in (1, 3, 11)

Upvotes: 3

Muzzammil
Muzzammil

Reputation: 1

Problem solved!

To select 2nd row in Oracle..

select SEN_NO,ITEM_NO from (select * from master_machine where 
sen_no ='BGCWKKL23'  and rownum <=2 order by 
rownum desc,timestamp asc) where rownum <=1

Thank You!

Upvotes: -2

sjngm
sjngm

Reputation: 12861

To address the reason for this:

The RowNum is a pseudo-column supplied by Oracle. It is generated while the SELECT-clause is being processed. Since the WHERE-clause is handled before the SELECT-clause, the RowNum does not have a proper value yet.

One can argue whether or not it makes sense to have Oracle throw an exception in situation, but because RowNum still is a pseudo-column it's still valid to have it there.

Note: Don't confuse this with RowId, which is an entire different story!

IMPORTANT EDIT:

Note that what I wrote about RowNum is only true for =, >, >=, IN () and maybe others. If you check for, e.g. RowNum < 10, you only get nine records!? I don't know why that is the case!

Upvotes: 7

davek
davek

Reputation: 22915

Based on the classic answer:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:127412348064

select * 
  from ( select a.*, rownum rnum
           from ( YOUR_QUERY_GOES_HERE -- including the order by ) a
          where rownum <= N_ROWS )
 where rnum >= N_ROWS
/

Upvotes: 31

stjohnroe
stjohnroe

Reputation: 3206

SELECT PRICE 
FROM (  
  SELECT PRICE,    
  ROWNUM rnum 
  FROM AAA_PRICING
  ORDER BY PRICE ASC
  ) 
WHERE    rnum = 2

Upvotes: 2

Michael Pakhantsov
Michael Pakhantsov

Reputation: 25370

Will not works with '=' (will works <2 or >2, but not equal)

so you can

 SELECT Price from (SELECT PRICE, ROWNUM AS RN FROM AAA_PRICING) WHERE RN = 2

Upvotes: 15

Related Questions