I have a table with data like.
ItemCode
1000
1002
1003
1020
1060
I'm trying to write a SQL statement to get the minimum number (ItemCode) that is NOT in this table and it should be able to get the next lowest number once the previous minimum order ID has been inserted in the table but also skip the numbers that are already in the DB. I only want to get 1 result each time the query is run.
So, it should get 1001
as the first result based on the table above. Once the ItemCode = 1001
has been inserted into the table, the next result it should get should be 1004
because 1000
to 1003
already exist in the table.
Based on everything I have seen online, I think, I have to use a While loop to do this. Here is my code which I'm still working on.
DECLARE @Count int
SET @Count= 0
WHILE Exists (Select ItemCode
from OITM
where itemCode like '10%'
AND convert(int,ItemCode) >= '1000'
and convert(int,ItemCode) <= '1060')
Begin
SET @COUNT = @COUNT + 1
select MIN(ItemCode) + @Count
from OITM
where itemCode like '10%'
AND convert(int,ItemCode) >= '1000'
and convert(int,ItemCode) <= '1060'
END
I feel like there has to be an easier way to accomplish this. Is there a way for me to say...
select the minimum number between 1000 and 1060 that doesn't exist in table X
EDIT: Creating a new table isn't an option in my case
Final Edit: Thanks guys! I got it. Here is my final query that returns exactly what I want. I knew I was making it too complicated for no reason!
With T0 as ( select convert(int,ItemCode) + row_number() over (order by convert(int,ItemCode)) as ItemCode
from OITM
where itemCode like '10%'
AND convert(int,ItemCode) >= '1000'
And convert(int,ItemCode) <= '1060')
Select MIN(convert(varchar,ItemCode)) as ItemCode
from T0
where convert(int,ItemCode) Not in (Select convert(int,ItemCode)
from OITM
where itemCode like '10%'
AND convert(int,ItemCode) >= '1000'
and convert(int,ItemCode) <= '1060');
This should do the thing. Here you are generating sequantial number for rows, then comparing each row with next row(done by joining condition), and filtering those rows only where difference is not 1, ordering by sequence and finally picking the top most.
;with c as(select id, row_number() over(order by id) rn)
select top 1 c1.id + 1 as NewID
from c as c1
join c as c2 on c1.rn + 1 = c2.rn
where c2.id - c1.id <> 1
order by c1.rn