How to find the n'th Row
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: How to find the n'th Row

  1. #1
    Join Date
    Dec 2001
    Posts
    11

    Question

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

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    what do you mean the n-th row....? you cannot determine the order of rows in oracle

  3. #3
    Join Date
    Dec 2001
    Posts
    11
    I am explaining my problem with example . Suppose there are 20 rows in a table and i want to select the 5th row.

  4. #4
    Join Date
    Feb 2001
    Posts
    125
    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

  5. #5
    Join Date
    Jan 2001
    Posts
    318
    eg to get 7th row you can do...
    select * from template where template_id = ( select max(template_id) from template where rownum<7)
    Sonali

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    aps you mean that, if that was the case you could use inline views

    Code:
    select * 
    from (select rownum numrow, emp.* from emp
             where rownum < 7)
    where numrow > 5
    that gives you the 6th row

  7. #7
    Join Date
    Nov 2001
    Location
    India
    Posts
    11
    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]

  8. #8
    Join Date
    Aug 2001
    Posts
    267
    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 ...
    Raghu

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by raghud
    select name from customer where rownum = 10 ;
    Well, this query will never ever return any row!
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width