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.
