SQL Server 2005 - Find Minimum Unused Value Within Range

Question

I have a situation similar to the following question:

Insert data into SQL table

Where is my scenario different in that I have a non-automatically increasing primary key field that can have a range from 1000 to 1999. We have only about a hundred values ​​in it so far, but the maximum value has already been accepted (1999), and there is spaces in the numbering sequence. So I need to find a value that is between 1000-1999 and not accepted. For example, if my current values ​​are, for example, 1000, 1001, 1003 and 1999, I would like the query to return 1002.

Answer 1

try the following:

declare @YourTable table (PK int) insert @YourTable VALUES (1) insert @YourTable VALUES (2) insert @YourTable VALUES (4) insert @YourTable VALUES (7) insert @YourTable VALUES (8) SELECT MIN(y.PK)+1 FROM @YourTable y LEFT OUTER JOIN @YourTable y2 ON y.PK+1=y2.PK WHERE y.PK>=1 AND y.PK<10 AND y2.PK IS NULL 

CONCLUSION:

 ----------- 3 (1 row(s) affected) 

EDIT
this will give the same result:

 ;with N AS (SELECT TOP 1000 row_number() over(order by t1.object_id) as Number FROM sys.objects t1 CROSS JOIN sys.objects t2 ) SELECT MIN(Number) AS PK FROM N LEFT OUTER JOIN @YourTable y on n.Number=y.PK WHERE y.PK IS Null 

Answer 2

The easiest way I can imagine is to create a table with values ​​1000-1999, then do the following:

 Select MIN(Values.Key) From Main Left Join Values on Values.Key = Main.Key Where Main.Key is Null 

But someone can probably come up with a more elegant solution.

Answer 3

EDIT : Looks like KM Hit me on this ... use his solution.

I have a different answer that may be better than my other.

 Select Min(Main1.Key)+1 From Main as Main1 Left Join Main as Main2 on Main1.Key+1=Main2.Key Where Main2.Key is Null 

This will find the lowest number that has no other number 1 above it.

Answer 4

Not quite sure if I misunderstood the question, but look at something like

 declare @YourTable table (PK int) insert @YourTable VALUES (1) insert @YourTable VALUES (2) insert @YourTable VALUES (4) insert @YourTable VALUES (7) insert @YourTable VALUES (8) SELECT TOP 1 * FROM @YourTable yt WHERE NOT EXISTS ( SELECT 1 FROM @YourTable WHERE yt.PK+1 = PK ) ORDER BY yt.PK 

Results in

 PK ----------- 2 

From where you can choose (2 + 1) X -).

Answer 5

This gives the correct answer, even if there are no entries in the selected range. It also allows you to group by field to get the set of the lowest values ​​by group. The SKU in this example refers to sequential numbers.

 SELECT A.myType, COALESCE (MIN( A.sku) + 1, 1) AS SKU /* 1 is Min Default + 1 */ FROM ( SELECT distinct myType, sku FROM dbo.myTable AS p1 where p1.sku > 0 /* zero is Min Value */ and p1.sku < 100 /* 100 is Max Value */ union select distinct myType, 0 as sku /* Guarantee Min Value */ from dbo.myTable as t1 ) AS A LEFT OUTER JOIN ( SELECT distinct myType, sku FROM dbo.myTable AS p2 where p2.sku > 0 /* zero is Min Value */ and p2.sku < 100 /* 100 is Max Value */ union select distinct myType, 0 as sku /* Guarantee Min Value */ from dbo.myTable as t2 ) AS B ON A.myType = B.myType AND A.sku + 1 = B.sku where (B.sku IS NULL) GROUP BY A.myType 

distinct does speed up the request, although union already executes distinct . As long as you have a pointer to myType and SKU, it should be instantaneous.

Obviously, you can replace 0 with the smallest and 100 with the highest allowable values.

Answer 6

from How to find a "space"? in running a counter with SQL?

 select MIN(ID) from ( select 1000 ID union all select [YourIdColumn]+1 from [YourTable] where --Filter the rest of your key-- ) foo left join [YourTable] on [YourIdColumn]=ID and --Filter the rest of your key-- where [YourIdColumn] is null