Organizational Research By

Surprising Reserch Topic

select nth row from a table in oracle


select nth row from a table in oracle  using -'sql,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!
    

asked Sep 9, 2015 by Debbie18R56
0 votes
10 views



Related Hot Questions

4 Answers

0 votes

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
/
answered Sep 9, 2015 by EnrDesailll
0 votes

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!

answered Sep 9, 2015 by BebeTeaguer
0 votes

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
answered Sep 9, 2015 by AraDobie
0 votes

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!

answered Sep 9, 2015 by VicBarksdal

...