Another good point Ron. This strategy allows update and delete activity on the table concurrently with the insert.
-----Original Message----- From: Reidy, Ron [mailto:[EMAIL PROTECTED] Sent: Wednesday, 15 September 2004 6:53 AM To: Steve Baldwin; NIPP, SCOTT V (SBCSI); [EMAIL PROTECTED] Subject: RE: SQL question: Find next unused number... Or ... SELECT MIN(id) FROM t FOR UPDATE; Would only cause a row lock. ----------------- Ron Reidy Lead DBA Array BioPharma, Inc. -----Original Message----- From: Steve Baldwin [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 14, 2004 2:50 PM To: Reidy, Ron; 'NIPP, SCOTT V (SBCSI)'; [EMAIL PROTECTED] Subject: RE: SQL question: Find next unused number... Good point Ron. However, if the requirement were to 'recycle' deleted ID's, you could obtain an exclusive lock on the table before issuing the SELECT. However, if you expect a large number of concurrent executions of this code, you would want to ensure the INSERT and subsequent COMMIT happens immediately after the SELECT. So it would go something along the lines of LOCK TABLE user_table IN EXCLUSIVE MODE ; SELECT MIN ... INSERT INTO user_table ... COMMIT ; There may be performance issues with this strategy, but if your requirement is definitely to 'fill in the gaps', you'll just have to take the hit. Steve -----Original Message----- From: Reidy, Ron [mailto:[EMAIL PROTECTED] Sent: Wednesday, 15 September 2004 6:43 AM To: Steve Baldwin; NIPP, SCOTT V (SBCSI); [EMAIL PROTECTED] Subject: RE: SQL question: Find next unused number... With Oracle, this will not work with many users executing the code at the same time. Better to use q sequence. ----------------- Ron Reidy Lead DBA Array BioPharma, Inc. -----Original Message----- From: Steve Baldwin [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 14, 2004 2:41 PM To: 'NIPP, SCOTT V (SBCSI)'; [EMAIL PROTECTED] Subject: RE: SQL question: Find next unused number... You didn't mention what DB you are using, but if it were Oracle, I would do something like this ... SELECT MIN (user_id) + 1 FROM user_table a WHERE userid >= 3000 AND NOT EXISTS ( SELECT 0 FROM user_table b WHERE b.user_id = a.user_id + 1) You would obviously want to ensure there is an index on the user_id column if the table has a lot of rows. HTH, Steve -----Original Message----- From: NIPP, SCOTT V (SBCSI) [mailto:[EMAIL PROTECTED] Sent: Wednesday, 15 September 2004 5:31 AM To: [EMAIL PROTECTED] Subject: SQL question: Find next unused number... I have a table that has a list of users with numeric user IDs. The user IDs are not sequential. There are large gaps in the list of numeric IDs. I want to pick the next unused number beginning at 3000. How do I do this? Thanks in advance. Scott Nipp Phone: (214) 858-1289 E-mail: [EMAIL PROTECTED] Web: http:\\ldsa.sbcld.sbc.com This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system. This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system.
