
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.

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

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"

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

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"

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

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

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

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

Forum Rules

Click Here to Expand Forum to Full Width
