first/last row in a table

[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.

[tamilselvan]

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

[dbasan]

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

[slimdave]

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 ...

select * from
(
select * from my_table order by insert_timestamp
)
where rownum = 1

[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 rownum works as i said:

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>

[davey23uk]

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

[slimdave]

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) ...

[slimdave]

... or where you've had deletes that left a space that has since been re-used by a new row, of course.

[mrchrispy]

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