How to find the n'th Row

[rajbabu]

Would any body provide me the script to find the n th row from a table?

[pando]

what do you mean the n-th row....? you cannot determine the order of rows in oracle

[rajbabu]

I am explaining my problem with example . Suppose there are 20 rows in a table and i want to select the 5th row.

[PSoni]

HI,


try this


select * from where rownum < 6
MINUS
select * from where rownum <5


this will return 5th row of the table.



thanks

P.Soni

[sonaliak]

eg to get 7th row you can do...
select * from template where template_id = ( select max(template_id) from template where rownum<7)

[pando]

aps you mean that, if that was the case you could use inline views

select * 
from (select rownum numrow, emp.* from emp
         where rownum < 7)
where numrow > 5

that gives you the 6th row

[safkhan]

If you want to get 5th row, try this query,

select * from tab1
where rowid = (select rowid from tab1 where rownum <=5
MINUSselecy rowid from tab1 where rownum < 5)


This query can also be used to get "Range of Rows". For eg. To retrieve 3rd to 9th row try thism

select * from tab1
where rowid in (
select rowid from tab1 where rownum <=9
MINUS
select * from tab1 where rownum < 3)






[Edited by safkhan on 12-10-2001 at 03:35 PM]

[raghud]

I don't the purpose to get the nth row .. But rownum might change from time to time .. For eg : If you run a query that returns "john" ...

select name from customer where rownum = 10 ;

If you run this again you may or maynot get "John" agian . Since rounum is not constant . The row order might change because off inserts ...

[pando]

thass why I mentioned in previous thread that you cannot determine the row order

the queries are good if you have asearch engine like front end and you are lookng for certain number of rows per fetch

[jmodic]

Originally posted by raghud
select name from customer where rownum = 10 ;

Well, this query will never ever return any row!