Find the Smallest Unused Number in SQL Server

Find the smallest unused number in SQL Server

Find the first row where there does not exist a row with Id + 1

SELECT TOP 1 t1.Id+1 
FROM table t1
WHERE NOT EXISTS(SELECT * FROM table t2 WHERE t2.Id = t1.Id + 1)
ORDER BY t1.Id

Edit:

To handle the special case where the lowest existing id is not 1, here is a ugly solution:

SELECT TOP 1 * FROM (
    SELECT t1.Id+1 AS Id
    FROM table t1
    WHERE NOT EXISTS(SELECT * FROM table t2 WHERE t2.Id = t1.Id + 1 )
    UNION 
    SELECT 1 AS Id
    WHERE NOT EXISTS (SELECT * FROM table t3 WHERE t3.Id = 1)) ot
ORDER BY 1

SQL - Find the lowest unused number

Assuming your number start from 1 always below query will give unused number

select min(rank) as Num from 
(select num,@curRank1 := @curRank1 + 1 AS rank from (SELECT num1 as num FROM t1 
UNION
SELECT num2 as num FROM t2) a1, (SELECT @curRank1 := 0) r ORDER BY num ASC) tab where num != rank;`

Fastest way to find the lowest missing number in a primary key in SQL Server

Does this really takes ages?

SELECT MIN(ID) + 1 
FROM TableName t1
WHERE NOT EXISTS
(
    SELECT 1 FROM TableName t2
    WHERE ID = t1.ID + 1
)

Edit: I have tested it on a table with ~100 million records and it really took some time, more than i thought. There was a missing primary key at 12522150 and the query took 31 seconds.

You could also use ROW_NUMBER, it works from SQL-Server 2005 on:

WITH CTE AS
(
    SELECT ID, RN = ROW_NUMBER() OVER (ORDER BY ID)
    FROM TableName
)
SELECT MIN(ID) + 1
FROM CTE
WHERE ID <> (RN - 1) + (SELECT MIN(ID) FROM TableName)

But this query needs even 41 seconds.

Both queries presume following rule:

• If the first value is 1000, it's not the first gap
• The first gap is 1002 if there are 1000,1001,1003

Get minimum unused value in MySQL column

SELECT min(unused) AS unused
FROM (
    SELECT MIN(t1.id)+1 as unused
    FROM yourTable AS t1
    WHERE NOT EXISTS (SELECT * FROM yourTable AS t2 WHERE t2.id = t1.id+1)
    UNION
    -- Special case for missing the first row
    SELECT 1
    FROM DUAL
    WHERE NOT EXISTS (SELECT * FROM yourTable WHERE id = 1)
) AS subquery

How to get the smallest unique integer from a table in SQLite

You may try aggregating your table by the number, restricting to numbers only appearing once, and then retaining the smallest number:

SELECT number
FROM yourTable
GROUP BY number
HAVING COUNT(*) = 1
ORDER BY number
LIMIT 1;

Smallest available integer in subset of table - SQL

How about something like this?

declare @SampleData table ([BeginDate] date, [EndDate] date, [Order] int);
insert @SampleData values
    ('2015-07-08', '2015-07-09', 0),
    ('2015-07-09', '2015-07-10', 1),
    ('2015-07-09', '2015-07-11', 2);

declare @Start date = '2015-07-10';
declare @End date = '2015-07-11';

with [OrderingCTE] as
(
    select
        [Order],
        [Ideal Order] = row_number() over (order by [Order]) - 1
    from
        @SampleData
    where
        [BeginDate] <= @End and
        [EndDate] >= @Start
)
select coalesce
(
    min(case [Order] when [Ideal Order] then null else [Ideal Order] end),
    max([Order]) + 1
)
from
    [OrderingCTE];

The CTE produces two orderings for each record in the source table: [Order] is the actual value stored in the record, and [Ideal Order] is what that value would be if all possible orderings (starting with zero) were in use within the given date range.

If at any point the [Ideal Order] differs from the [Order], you can infer that the current [Ideal Order] value has not been used and is therefore the minimum available value. If this is not true at any point, then the minimum available value is one greater than the largest value that has been used thus far; that's the second half of the COALESCE at the bottom of the script.

As a final note: the question you linked has another answer raises concerns about a possible race condition that can arise, depending on how you're trying to use the data that you query in this way. I'd strongly recommend taking a look at it if you haven't already done so.

(VB.NET 2010) How to write an SQL query which selects the lowest unused number

select min(StudentID) + 1
from Students s1
where not exists (select 1 from Students s2
                  where s2.StudentID = s1.StudentID + 1)

---

Related Topics