Getting the next code - 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)

Getting the next code

Author

Topic

erwine
Yak Posting Veteran

69 Posts

Posted - 2006-07-26 : 05:17:47

Hi y'all.

create table MYTABLE
( code integer)

insert into MYTABLE
select 1 union all
select 2 union all
select 5

and i now have:
Code
1
2
5

how can i get the next unused code, in this case 3 instead of getting 6? imagine that if my records:
10002
10050
10051
10052
i want to get the next code = 10003..until 10003-10049 is filled, then it moves to 10053.
anyone??
thx for ur kind attention.

regards,
erwine

... sql is fun...

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-26 : 05:22:22

select code = min(code + 1)
from MYTABLE t
where not exists (select * from MYTABLE t1 where t.code + 1 = t1.code)

see
http://www.nigelrivett.net/SQLTsql/FindGapsInSequence.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-26 : 05:25:16

Try this...

declare @test table
(
	a integer
)

insert into @test
select 1 union all
select 2 union all
select 5

select min(a) from (select min(a)+1 as a from @test
group by a
having min(a)+1 not in (select a from @test)) t

Harsh Athalye
India.
"Nothing is Impossible"

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-26 : 05:54:30

nr's not exists should be more efficient. We might want to handle the table being empty too. Here's a full example...

--data
create table MYTABLE (code integer primary key)
insert MYTABLE
          select 1
union all select 2
union all select 5

--calculation
declare @nextCode int

select @nextCode = isnull(min(code + 1), 1)
from MYTABLE t
where not exists (select * from MYTABLE where code = t.code + 1)

select @nextCode

--tidy
go
drop table MYTABLE

/*results
----------- 
3
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources