INT - Find Lowest Open ID to use, within a range

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 2005 Forums

Transact-SQL (2005)

INT - Find Lowest Open ID to use, within a range

Author

Topic

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2008-10-13 : 16:20:07

I'm trying to find a way to select the lowest 'hole' in a table that holds ID fields for instance, the hole would be between two parameters that I passs to the stored proc

@low int = 1
@high int = 10

Table1
ID
---------
1
2
3
5
6
7
9

I would expect my select statement or forumula to return 4

@low int = 13
@high int = 1000

Table1
ID
---------
12
22
32
52
62
72
92

I would expect my select statement or forumula to return 13

Is there a clean way to do this in SQL? I may solve it in .NET instead, but SQL would me quicker CPU wise.
Thanks.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-13 : 16:46:37

you can do it with a temp table:


--SETUP WORK TABLE
drop table #mytab
GO
create table #mytab (ID int)
GO
insert into #mytab
select 1
union all 
select 2
union all 
select 3
union all 
select 5
union all 
select 6
union all 
select 7
union all 
select 9
UNION ALL
select 12
union all
select 22
union all
select 32
union all
select 52
union all
select 62
union all
select 72
union all
select 92
GO

-----------You can make this a SP or UDF later----------------

declare @sqlstr varchar(8000)
declare @low int
declare @high int

set @low  = 13
set @high  = 99


set @sqlstr ='declare @checktab table(test1 int identity('+cast(@low as varchar)+',1), id int) 

insert into @checktab (ID)
select [ID] 
from #mytab 
where ID between '+cast(@low as varchar)+' and '+cast(@high as varchar)+'
order by ID

select min(test1) from @checktab where test1 <> ID'

print @sqlstr

execute(@sqlstr)

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-10-13 : 17:48:12

This should work if in case you don't want to use a temptable,

declare @low int
declare @high int

--set @low=1
--set @high=10

set @low=13
set @high=1000
select top 1 * from
(
select case when sum(1) is null then @low else '' end as a from test where col1=@low
union all
select col1+1 as a from test where col1 between @low and @high and col1+1 not in (select col1 from test)
)z
where a<>''
order by 1 asc

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-13 : 18:14:32

very close:

won't work if
@low = 1
@high = 3

or @low is out of range

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-13 : 18:16:20

Fixed:



declare @low int
declare @high int

--set @low=1
--set @high=10

set @low=13
set @high=1000
select top 1 * from
(
select case when sum(1) is null then @low else '' end as a from test where col1=@low
union all
select col1+1 as a from test where col1 between @low and @high and col1+1 not in (select col1 from test)
)z
where a between @low and @high
order by 1 asc

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-10-13 : 18:39:50

quote:

Originally posted by hanbingl

Fixed:



declare @low int
declare @high int

--set @low=1
--set @high=10

set @low=13
set @high=1000
select top 1 * from
(
select case when sum(1) is null then @low else '' end as a from test where col1=@low
union all
select col1+1 as a from test where col1 between @low and @high and col1+1 not in (select col1 from test)
)z
where a between @low and @high
order by 1 asc

ah, nice catch !

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2008-10-14 : 09:34:25

hanbingl & sakets_2000, thanks.

The 'fixed' version works perfectly for me.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 11:01:18

[code]DECLARE @Sample TABLE
(
ID INT
)

INSERT @Sample
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 9 UNION ALL
SELECT 12 UNION ALL
SELECT 22 UNION ALL
SELECT 32 UNION ALL
SELECT 52 UNION ALL
SELECT 62 UNION ALL
SELECT 72 UNION ALL
SELECT 92

DECLARE @Low INT,
@High INT

SELECT @Low = -10,
@High = 22

--Peso
SELECT MIN(s.ID) AS ID
FROM (
SELECT x.ID + 1 AS ID
FROM @Sample AS x
WHERE x.ID BETWEEN @Low - 1 AND @High - 1
) AS s
WHERE s.ID BETWEEN @Low AND @High
AND NOT EXISTS (SELECT * FROM @Sample AS t WHERE t.ID = s.ID)

-- hanbingl & sakets2000
select top 1 * from
(
select case when sum(1) is null then @low else '' end as a from @sample where id=@low
union all
select id+1 as a from @sample where id between @low and @high and id+1 not in (select id from @sample)
)z
where a between @low and @high
order by 1 asc[/code]

Peso 4
Other -10

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 15:10:57

Also see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=112538

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources