Is there a reason you want to use 3000 as a base? If there are no IDs >=
3000, then just start with 3000 and increment. If there are ID values
that exceed 3000, do you want to start AFTER those values, or.... if you
have 3000, 3001, 3002, 3010 in the table, do you want the next value to be
3003 (the first available number after 3000)? Or would you want the next
value to be 3011 (the next greatest value after what already exists)?
If you just want the next greatest number, you can do a SELECT MAX(ID)
FROM table and then increment that value for each subsequent insert. If you're
using 3000 as a base to start with, is there a possibility of creating a
sequence for that table? Using Oracle as an example, you could create a
sequence for a table that starts at 3000 and increments by one for each
new insert. Then, you just say INSERT INTO table VALUES(TABLE_SEQUENCE.NEXTVAL,
'such', 'such', 'and such', . . . );
Christopher R. Marbach
Data Quality Coordinator
Pharmacy Analytical Services
Kaiser Permanente
8-345-2485
(562) 401-2485
Tim Bunce <[EMAIL PROTECTED]>
09/14/2004 12:57 PM
To: "NIPP, SCOTT V (SBCSI)" <[EMAIL PROTECTED]>
cc: [EMAIL PROTECTED]
Subject: Re: SQL question: Find next unused number...
On Tue, Sep 14, 2004 at 02:30:43PM -0500, NIPP, SCOTT V (SBCSI) wrote:
> 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?
Off the top of my head, without checking it, something along these
lines might work:
SELECT t1.id+1
FROM table t1 LEFT OUTER JOIN table t2 ON (t2.id = t1.id
+ 1)
WHERE t2.id IS NULL
AND t1.id >= 3000
Tim.
