Getting the last record in a table.

[Pushkin]

Can anybody tell me how I can get the last record from a table.
e.g. I created a table with 3 fields id, birthdate and name.
I filled the table with data.
How can I go to the last data I hava inserted? How can I know what was the last ID I inserted?

I only need the value of the final ID.

Thanks in advance

Pushkin

 

[stefes007]

Unfortunably, the database engine doesn't write the records in an ordered method: you should then use a fourth column that you would implement by a trigger which would insert into that column the max value of that column + 1.

Then what you could do is:

select * from table_name where rownum = (select count(*) from table_name) order by your_new_column

OR

select * from table_name where your_new_column = (select max(your_new_column) from table_name)

Hope I could help you.

 

[ppqqrrsstt]

U can write foll. query to get last inserted row in table

select * from table_name where rowid = ( select max(rowid) from table_name)

 

[sem]

The first statement will return no rows regerdless on table data, so you may use only the second one.
To fill your_new_column use sequence, this will be much more efficient.

 

[stefanhei]

Another way would be to insert the sysdate in the trigger.
You can't rely on rowid. It also contains the file# => if a new extent is stored into another file you get wrong data.

Stefan (back from holiday)

 

[sem]

The drawback of using sysdate is that the accuracy is limited and a number of consecutive transactions may be commited during 1 second.

 

[stefanhei]

@sem
You are right, for (probably automated) massdata-transactions sysdate is too inaccurate, but what for one wants to know the last record of such a transaction.
Also for a multiuser-environment the second alone is to inaccurate.
I usualy use this method for tables with user-initiated transactions, and insert an user-identification in the same trigger too.

Stefan

 

[Turkbear]

If you want to actually track the ORDER of the changes as well as the user,( to avoid needing to know the milliseconds of the timestamp), you can modify stefanhei's technique and add a sequence number that is handled by the trigger that records the timestamp.