Getting the last record

Discussion in 'Databases' started by tanvirtonu, Dec 7, 2007.

0
  1. #1
    I need to generate my primary key from my application bcos my PK has a specific format(e.g. Cl2006-09). Now to do this I need to know the last record/primary key of the last row(or only one record if exists only one)so dat I can generate my new PK by adding up to this last value. By which query I can know only the last PK of my table (or only one PK if only one exists.)
     
    tanvirtonu, Dec 7, 2007 IP
  2. drunnells

    drunnells Peon

    Messages:
    79
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Maybe you could have a timestamp column in this table and then query the primary key from the last row inserted based on the timestamp? Like:

    SELECT primarykey FROM mytable ORDER BY timestamp DESC LIMIT 1;

    And then have your script analyze the primarykey and generate the next one for the next insert? This would make your inserts a two query job (one to fetch the last used primary key and one to do the actual insert), but if having the sql server generate auto-incremented primary keys is out of the question.. i can't really think of anything else..
     
    drunnells, Dec 7, 2007 IP
  3. satusaja

    satusaja Active Member

    Messages:
    108
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    53
    #3
    It is best to use GUID or sequence.

    Using your query last record method, it can fail in a case where 2 or more processes query the record at the same time and get the same value. The first process will succesfully inserted the record. The remaining will fail due to PK constraint.
     
    satusaja, Dec 8, 2007 IP
  4. tanvirtonu

    tanvirtonu Peon

    Messages:
    32
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #4
    I want to insert data in two tables from a single from. And these two tables has a parent-child relationship. Now if I insert data by the following query, will there be any problem? I mean- I want to insert tha same PK for the child table as its FK. And to do this, I will make both PK and Fk auto-increment integer field and first insert data in parent table and then child leaving the Pk and Fk field null.
    
Insert into parentTable values(null,'John','Doctor');
Insert into childTable values(null,'ABC Hospital','Address');
    Code (markup):
    in Db table can I have these values-
    parentTable-
    1 John Doctor
    childTable-
    1 ABCHospital Address

    what is "GUID or sequence."
     
    tanvirtonu, Dec 8, 2007 IP