first/last row in a table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: first/last row in a table

  1. #1
    Join Date
    Jul 2001
    Location
    Montreal
    Posts
    218

    first/last row in a table

    Hi, How do I get the first and last row in a table? Is there a function available? (min ? max?...row_id) Not sure how to do it? Please help. Thank you.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by pascal01
    Hi, How do I get the first and last row in a table? Is there a function available? (min ? max?...row_id) Not sure how to do it? Please help. Thank you.

    There is NO first row and last row in a database.

    Tamil

  3. #3
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Quote Originally Posted by pascal01
    Hi, How do I get the first and last row in a table? Is there a function available? (min ? max?...row_id) Not sure how to do it? Please help. Thank you.
    use rownum=1 in where clause will get you the first and to get the last ???
    there are ways to do..........Find it
    "What is past is PROLOGUE"

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by dbasan
    use rownum=1 in where clause will get you the first and to get the last ???
    there are ways to do..........Find it
    Wrong, I'm afraid. As TS says there is no concept of "first" or "last" row in a relation.

    Pascal, what you need is a column that defines for you which row is "first" or "last" ... something like "insert_timestamp", so that you could ...
    Code:
    select * from
    (
    select * from my_table order by insert_timestamp
    )
    where rownum = 1
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Pascal01 needs the first row and the last row as inserted in the table. I know there is no first and last row concept in Oracle. He needs the way data are entered in the table first callled first data and the last as last data as it is available at a point in time. Guys test for yourself rownum works as i said:

    Code:
    SQL>  create table san (i integer);
    
    Table created.
    
    SQL> insert into san values(1);
    
    1 row created.
    
    SQL> insert into san values(3);
    
    1 row created.
    
    SQL> insert into san values(5);
    
    1 row created.
    
    SQL> insert into san values(6);
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> delete from san where i=1;
    
    1 row deleted.
    
    SQL> select * from san;
    
             I
    ----------
             3
             5
             6
    
    SQL> select * from san where rownum=1;
    
             I
    ----------
             3
    
    SQL> rollback;
    
    Rollback complete.
    
    SQL> select * from san where rownum=1;
    
             I
    ----------
             1
    
    SQL> select * from san;
    
             I
    ----------
             1
             3
             5
             6
    
    SQL>
    "What is past is PROLOGUE"

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    no, rownum is applied to the *result set* not the data in the table

    so whatever row comes first from the table will be given rownum = 1, and the data will come out of the table in *ANY* order unless an order by is supplied - basic heap table concepts

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by dbasan
    Pascal01 needs the first row and the last row as inserted in the table. I know there is no first and last row concept in Oracle. He needs the way data are entered in the table first callled first data and the last as last data as it is available at a point in time. Guys test for yourself
    Now try it on an index-organized table, or a cluster, or a partitioned table, or in a parallel query environment, or where there's an index on your column "I" (especially a descending index) ...
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    ... or where you've had deletes that left a space that has since been re-used by a new row, of course.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Mar 2002
    Location
    Manchester, England
    Posts
    202
    I'm with slim and davey on this, there is no first and last row.....there are just rows, its up to you how the data is used, you'd need something like a sequence or timestamp colum to order the rows in your SQL

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