...

View Full Version : SQL query for last record added



Bengal313
08-25-2003, 05:41 AM
I am having trouble creating a recordset from the last recordset in a database. Can some one share how would you write an sql statement that would retreive the last record inserted. Here is the specs.

Database name: dbdog
tablename: owner
fields ID, fname, lname,address

Note : "ID" is an autonumber.

raf
08-25-2003, 07:43 AM
depends on the db-format.

For MySQL, check out this sticky http://www.codingforums.com/showthread.php?s=&threadid=22879

DaiWelsh
08-28-2003, 04:30 PM
As you said autonumber you may be dealing with MS Access (my sympathy) in which case afaik there is no built in way to get last insert id, so

SELECT TOP 1 ID FROM owner ORDER BY ID DESC

is probably the best you will get and in a light load environment this will work 99.xx% of the time.

MS SQL, MySQL, PostGreSQL etc. all have built in methods for it.

HTH,

Dai

raf
08-28-2003, 10:04 PM
That's not really bulletproof in a multiuser enviroment. It would be safer to add some sort of condition, where you check on one of the just inserted values. (the username of the one that made the insert, the registratipntim --> must first be 'frozen' in a variable, etc )like

SELECT TOP 1 ID FROM owner WHERE reguser='theuser' ORDER BY ID DESC

+
also only run this if you checkt that the record indeed was inserted



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum