I’m always looking for novel ways to accomplish something. Even if it’s totally inefficient, it still is a good exercise in problem-solving. Sometimes you can find a good challenge by taking something simple, then making it complex. For example, there is a simple way to get the first missing value within a non-contiguous range.
declare @i int=1
create table #t(value int)
-- Add numbers in multiples of 4
while @i<100
begin
if (@i % 4)=0
insert #t(value)values(@i)
set @i=@i+1
end
-- Get first available value
select top 1 t1.value+1
from #t t1
where not exists (
select 1
from #t t2
where t2.value=t1.value+1
)
order by 1
drop table #t
You can get all the next available numbers by removing the “top 1”, but what if the gap was more than 1 value wide? You’d have to get the first missing values, then fill them or track them in a temp table/variable, then call the statement again. In this example, we do have gaps larger than one value. We want to return 1,2,3,5,6,7,9,10,11, etc. This means we need to check for every value that is not in the #t table. How would you be able to do that in a single statement? In pseudo-code, you want to do something like:
select x where not exists (select 1 from #t)
but you have to have a FROM clause. And that means you have to have the potential values somewhere to pull from. You could make a temp table/variable and populate it with a range of values you want to compare against, but again, we want this to be done in one statement.
To solve this, you can make a small lookup table using UNPIVOT
select v
from (
select 0 v0,1 v1,2 v2,3 v3,4 v4,5 v5,6 v6,7 v7, 8 v8, 9 v9
) n
unpivot(v for value in (v0,v1,v2,v3,v4,v5,v6,v7,v8,v9)) n
This gets you a resultset of 0 through 9. Not a lot of values to compare against. But structuring it as a CTE, you can make this 0-99. You can join it again to get 0-999, and keep joining until you get the maximum values you need.
with Numbers(num) as
(
select v
from (
select 0 v0,1 v1,2 v2,3 v3,4 v4,5 v5,6 v6,7 v7, 8 v8, 9 v9
) n
unpivot(v for value in (v0,v1,v2,v3,v4,v5,v6,v7,v8,v9)) n
)
select (N1.num*10)+N2.num
from Numbers N1
full join Numbers N2 on 1=1
Now that we have a table of lookup values, it’s trivial to find the missing values from our original table.
with Numbers(num) as
(
select v
from (
select 0 v0,1 v1,2 v2,3 v3,4 v4,5 v5,6 v6,7 v7, 8 v8, 9 v9
) n
unpivot(v for value in (v0,v1,v2,v3,v4,v5,v6,v7,v8,v9)) n
)
select lookupNum
from (
select (N1.num*10)+N2.num lookupNum
from Numbers N1
full join Numbers N2 on 1=1
) n
where not exists(
select 1
from #t t
where t.value=n.lookupnum
)
order by 1
One complete statement. Whee!