SQL Server 2005 - Find minimum unused value within a range

I have a situation similar to the following question:

Insert Data Into SQL Table

Where my scenario differs is that I have a non-auto-incrementing primary key field that can have a range between 1000 and 1999. We only have about a hundred values in it thus far, but the maximum value has already开发者_运维问答 been taken (1999), and there are gaps in the numbering sequence. Thus, I need to find a value that is between 1000-1999 and not taken. For instance, if my current values are, for example, 1000, 1001, 1003, and 1999, I would want the query to return 1002.

try this:

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

OUTPUT:

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

The simplest way I can think of 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 else can probably come up with a more elegant solution

EDIT: Looks like KM Beat me to it... use his solution.

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

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 doesn't have another number 1 above it.

Not quite sure if I misunderstood the question but have a 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 pick (2 + 1) X-).

This produces a correct answer even if there are no records within the selected range. This also allows grouping by a field to get a set of lowest values by group. SKU in this example refers to the 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

The distinct actually speeds up the query even though union already does a distinct. As long as you have an index on myType and SKU, it should be instant.

Obviously, you can replace 0 with the lowest and 100 with the highest allowed values.

from How do I find a "gap" in running 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