Subject: RE: SQL question: Find next unused number... - msg#00185

Well... The database is actually MySQL. In answer to a couple
of other questions... The reason for the number beginning at 3000 is
that this if for a Unix user account system, and we are reserving all
numeric UIDs below 3000. Thanks again for the feedback. Still trying
to figure this out.

Scott Nipp
Phone: (214) 858-1289
E-mail: sn4265@xxxxxxx
Web: http:\\ldsa.sbcld.sbc.com



-----Original Message-----
From: Steve Baldwin [mailto:stbaldwin@xxxxxxxxxxxxxxxx]
Sent: Tuesday, September 14, 2004 4:02 PM
To: 'Reidy, Ron'; NIPP, SCOTT V (SBCSI); dbi-users@xxxxxxxx
Subject: RE: SQL question: Find next unused number...


Another good point Ron. This strategy allows update and delete activity
on
the table concurrently with the insert.

-----Original Message-----
From: Reidy, Ron [mailto:Ron.Reidy@xxxxxxxxxxxxxxxxxx]
Sent: Wednesday, 15 September 2004 6:53 AM
To: Steve Baldwin; NIPP, SCOTT V (SBCSI); dbi-users@xxxxxxxx
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:stbaldwin@xxxxxxxxxxxxxxxx]
Sent: Tuesday, September 14, 2004 2:50 PM
To: Reidy, Ron; 'NIPP, SCOTT V (SBCSI)'; dbi-users@xxxxxxxx
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:Ron.Reidy@xxxxxxxxxxxxxxxxxx]
Sent: Wednesday, 15 September 2004 6:43 AM
To: Steve Baldwin; NIPP, SCOTT V (SBCSI); dbi-users@xxxxxxxx
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:stbaldwin@xxxxxxxxxxxxxxxx]
Sent: Tuesday, September 14, 2004 2:41 PM
To: 'NIPP, SCOTT V (SBCSI)'; dbi-users@xxxxxxxx
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:sn4265@xxxxxxx]
Sent: Wednesday, 15 September 2004 5:31 AM
To: dbi-users@xxxxxxxx
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: sn4265@xxxxxxx
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.