Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2004
    Posts
    7

    Exclamation Unanswered: Replacement for MAX statement?

    Hi all :
    I got a problem here.

    The scenario :
    The serial key code of a table is auto generate, and currently I'm using MAX to return the latest serial key code. The problem is, if the table grow bigger and access by multiple user at one time ( I think select statement does not have any row or table lock) the MAX key will return the wrong serial key code or even fail at some point. The serial key code is the only unique key in the table.

    Can anyone give me some guide or tips on how to resolve this? Or other way of implementing the select statement to avoid usage of MAX?

    Thanks in advance.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the best way to avoid this is not to return the latest serial key at all

    what do you need it for?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2004
    Posts
    7
    I need to get the serial key code as a foreign key to insert into another table. The Serial key code will server as a customer ID in my case. since no customer ID can be and should be duplicate, I need to get the serial key code.

    I had figure out a way, using a select * statement and use a while loop to loop thru the whole result set collection. From there I can get the last serial key code.

    I got a question here, if I use this way, will it be less effective than using MAX? Consider I got a table with more than 5000 record.

    Thanks.

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    On the table you're inserting into, will the SerialKeyCode be unique, or will you have a one-to-many relationship?
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  5. #5
    Join Date
    Jul 2004
    Posts
    7
    Serial Key Code will be generate automatically by the database and been set to unique with increament by 1 everytime new record is add.

  6. #6
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Quote Originally Posted by ocmeng
    The problem is, if the table grow bigger and access by multiple user at one time ( I think select statement does not have any row or table lock) the MAX key will return the wrong serial key code or even fail at some point. Thanks in
    If you return the maximum serial ID at 14:00 and then again at 14:01 they may indeed be different. I don't see a problem. If you wish to keep an accurate record, then you could implement an insert trigger to store the most recent serial code in another table.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here's what you do --

    insert a row, and let the database automatically assign the key

    use the provided database function to retrieve the value of the key that was assigned

    in SQL Server, this is the @@identity function, in mysql it is mysql_insert_id(), use whatever function your database provides

    if your database does not provide such a function, then simply query back the row that you just inserted, not with MAX but with the values of the other columns

    then use the value of the key as the foreign key when you insert the related record

    for example, if you insert a username/password combination of 'fred','sesame' into a user table, then you instead of selecting MAX or (shudder!) returning the entire table to see the last one, just run this query:

    select userid from users where username='fred' and password='sesame'

    the automatically incremented userid value that you get back will be what you use to insert the related row in the other table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jul 2004
    Posts
    7
    Thanks for your suggestion. The problem is that there r no unique key in the table except the auto serial key code assign by the database. I can not use the second method u suggest. About the first suggestion, it's great, but only if I know what kind of statement the databse provide for me to get the number assign. I'm using Informix. It would be great if you can give some hints on the command to use to get the sequence number. Thanks

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ah, informix

    look up NEXTVAL and CURRVAL in the manual

    the serial number is actually separate from the table, and you use NEXTVAL to get the next value that you insert into the table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jul 2004
    Posts
    7
    The process is like this :

    1)Insert into table A
    2)Get the result set from table A (for the latest serial key code)

    What you try to said is that I need to use NEXTVAL before step 1 to get the serial key code I need.

    I'm using Java. I need to write SQL statement for NEXTVAL or in the Java Code.

    Would be appreciate if you can give any hints. TY.

  11. #11
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Insert into tableA values (seq.nextval, column1, column2)
    select * from tableA where id = seq.currVal
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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