How to get MIN free id? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to get MIN free id?

Author

Topic

milank
Starting Member

2 Posts

Posted - 2004-09-09 : 03:18:21

Hallo,
how could I get (in T-SQL) the first free ID in a discontinuous sequence?
A table has a column ID (integer, not identity) and I don't want to assign to the next record ID=MAX(ID)+1, but the first free.

It means something like "SELECT MIN(id) FROM tbl a WHERE id NOT IN (SELECT id FROM tbl)" ... which doesn't work, of course

Any ideas?
Thanks in advance

mr_mist
Grunnio

1870 Posts

Posted - 2004-09-09 : 04:20:10

Without having a table of numbers, here's one way. I think that it would be inefficient for large tables.



create table moo (moo int)

insert into moo values (1)
insert into moo values (2)
insert into moo values (4)
insert into moo values (5)
insert into moo values (6)

SELECT min(a.moo) + 1 as 'smallest unused value' -- a.moo, b.moo 
from moo a
left outer join moo b
on b.moo = a.moo +1
where b.moo is null

drop table moo

-------
Moo. :)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-09 : 04:45:08

[code]create table #mytable( id int )

insert #mytable(id)
select 1 union select 2 union select 3 union select 4 union select 6

select min(next_id) from(
select top 1
count(*) as next_id
from
#mytable a
join #mytable b on a.id >= b.id
group by a.id
having a.id > count(*)
union /*in case no gaps in sequence*/
select max(id)+1 from #mytable ) ids

drop table #mytable[/code]

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-09 : 04:51:18

Yea moo,
mine gives 1 for sequence 2,3,4,5.
Yours is nicer though

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-09 : 04:53:18

Jen - uuh no,
because the table datatype would bo out of scope inside the derived table ids in my example.

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

milank
Starting Member

2 Posts

Posted - 2004-09-09 : 08:21:00

Thanks again,
my friend invented another solutions:
SELECT MIN(a.id)+1 FROM tbl a WHERE a.id+1 NOT IN
(SELECT b.id FROM tbl a JOIN tbl b ON a.id+1=b.id)

and the next (slightly better):
SELECT MIN(a.id)+1 FROM tbl a WHERE NOT EXISTS
(SELECT b.id FROM tbl b JOIN tbl c ON c.id+1=b.id WHERE a.id+1=b.id)

but the the one of mr_mist is the best!

mr_mist
Grunnio

1870 Posts

Posted - 2004-09-09 : 08:30:21

quote:
Originally posted by milank

but the the one of mr_mist is the best!

Thanks. Please take note of Rockmoose's comment that my example misses 1 for sequence 2,3,4,5. (IE it misses any result lower than the lowest value in the table).

-------
Moo. :)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-09 : 08:53:10

mr_mist - you could easliy fix that

quote:


create table moo (moo int)

insert into moo values (1)
insert into moo values (2)
insert into moo values (4)
insert into moo values (5)
insert into moo values (6)

SELECT min(a.moo) - 1 as 'smallest unused value' -- a.moo, b.moo 
from moo a
left outer join moo b
on b.moo = a.moo - 1
where a.moo>1
and b.moo is null


drop table moo

Corey

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-09 : 08:58:05

Just waiting for that

!!!

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-09 : 09:00:17

I have to put my 2¢ in

Corey

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-09 : 09:36:30

that last solution by Corey wouldn't the next highest ID, though, if there were no gaps in the sequence, which i assume any solution would have to allow for.

- Jeff

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-09 : 09:53:54

True, true:


SELECT min(a.moo) - 1 as 'smallest unused value' -- a.moo, b.moo 
from (Select moo From moo Union Select moo = max(moo)+2 From moo) a
left outer join moo b
on b.moo = a.moo - 1
where a.moo>1
and b.moo is null

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources