What would be the correct universal SQL construct to get the last row inserted (or it's primary key). The ID might be autogenerated by a sequence but I do not want to deal with the sequence at all! I need to get the ID by querying the table. Alternatively, INSERT might be somehow extended to return the ID. Assume I am always inserting a single row. The solution should work with most RDBMS!
+1
A:
the best way is to depend on the sequence like:
select Max(ID) from tableName
but If you don't want to deal with it, you can add new timestamp column to your table and then select max from that column.
like this way
select Max(TimestampField) from tableName
Wael Dalloul
2009-08-25 08:50:49
Adding a timestamp is not an option for me. My app must not change table structure.
ˈoʊ sɪks
2009-08-25 09:11:48
The sequence solution is working for me. It is probably worth noting Max(ID) will yield an incorrect result if your seqeunce rolls over (cycles over). In my case this is irrelevant. Thanks.
ˈoʊ sɪks
2009-08-25 12:04:47
You need to make sure your isolation level is such, that you will not see IDs inserted by other transactions. And max(TimeStampField) will require that writes not come too often, so that no two writes are within the resolution of that field.
Shannon Severance
2009-08-25 16:54:59