Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder Bengal313's Avatar
    Join Date
    Jun 2002
    Location
    Metro Detroit
    Posts
    298
    Thanks
    8
    Thanked 0 Times in 0 Posts

    SQL query for last record added

    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.
    "Knowledge Speaks But Wisdom Listens"

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    depends on the db-format.

    For MySQL, check out this sticky Getting the last insert id of a record

  • #3
    New Coder
    Join Date
    Aug 2003
    Location
    Derby, UK
    Posts
    97
    Thanks
    0
    Thanked 14 Times in 14 Posts
    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

  • #4
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •