How to find the last row inserted

[dickiebird]

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?
On Oracle I'd do :
select * from tablcode where rowid = ( select max(rowid) from tablcode)
But this is Sybase.........
Thanks in Advance
;-)
Dickie Bird
db@dickiebird.freeserve.co.uk

 

[JMCraig]

Hi Dickie,

Since you don't have a key on the table that is related to the insertion order (unless ID is, and I assume it's not or you'd have used it), I don't know of a reliable way to deal with this (unless you have no clustered index on the table, in which case there's a really lame way to do it--let me know if you're interested in lame solutions, okay?).

The heart of the difference between Oracle and Sybase here is that Sybase generally is configured to use page-level locking (which is faster and in many cases, puts no substantial burden on concurrent access to rows). This means that Sybase does not have to attach a unique identifier to each row (unless you want it to). Oracle, on the other hand, does row level locking so it has to have a unique row identifier (which you can exploit as described in your post).

Here's some things you can do to give yourself the kind of acces you need:

1. TIMESTAMP column
If you add a timestamp column to the table, you could look for the max(ts_column_name) row. To do this with data already in the table, you'll have to alter the table and allow it to copy the data into the new structure (use NOT NULL with the ALTER TABLE command's column info) or add it to allow NULL's, and then perform a no-op update like this one to set the timestamp for existing rows:

update my_table
set id = id

This will set the TIMESTAMP value. Note that TIMESTAMP isn't a "time" stamp at all, but just a guaranteed unique 8 byte value that is always larger than the last one (I suppose at some point the thing must roll over, but in a practical amount of time, I don't think that's likely).

2. IDENTITY column
The Sybase equivalent to the row identifier in Oracle is an IDENTITY column (basically an autoincremented surrogate key); you access the value of the IDENTITY column using special syntax. There's a fairly good explanation in the Transact-SQL User's Guide and more info in the reference manuals.

One possible advantage to the use of an IDENTITY column over a TIMESTAMP is that IDENTITY doesn't come back when you do a SELECT * from the table--you only get it if you follow the special syntax. Conversely, TIMESTAMP columns are treated just like a regular data column by SELECT *.

Either of those should give you the way you need to access the latest added row in a table (at the cost of another column). You would think that you could also change the table's locking scheme to datarows and also get an automatic indication of each row's ID--clearly this must happen at some level, but I've either missed the explanation in the docs or there's no way to get at it.

Best of luck,

John J M Craig
Alpha-G Consulting, LLC

http://alphagconsulting.bizhosting.com

nsjmcraig@netscape.net

 

[dickiebird]

Many thanks for your reply, John
I'll create a new table with column type 'identity'
and insert into, from my existing table.
Dickie Bird
db@dickiebird.freeserve.co.uk