How do I get the next available max number?

Question

Did my title make sense? i'm sorry if it didn't.

From this column, how do I query so that I can get 5 as a result?

Number
-----
  1
  2
  3
  4
  6
  7

Answer 1

Edited http://www.sqlservercentral.com/Forums/Topic911849-392-1.aspx for your specs - To see all missing numbers delete the min function:

;WITH Tally(N) AS
(
  SELECT number N FROM master.dbo.spt_values WHERE Type = 'P' AND number > 0
)
SELECT
  min(T.N )
FROM
  Tally T
LEFT JOIN
  numbers MN ON MN.N = T.N
WHERE
  MN.N IS NULL
  AND T.N <= (SELECT MAX(N) FROM numbers)

Answer 2

You want a narrower version of this: SQL: find missing IDs in a table

plus: In SQL, how do you get the top N rows ordered by a certain column?

So you would add LIMIT 0, 1 to the query in MySQL

Or you would add TOP 1 to the SELECT for MS SQL

In MySQL:

declare @id int
declare @maxid int

set @id = 1
select @maxid = max(id) from tbl

create temporary table IDSeq
(
    id int
)

while @id < @maxid
begin
    insert into IDSeq values(@id)

    set @id = @id + 1
end

select 
    s.id 
from 
    idseq s 
    left join tbl t on 
        s.id = t.id 
 where t.id is null
 LIMIT 0, 1

 drop table IDSeq

In SQL Server:

declare @id int
declare @maxid int

set @id = 1
select @maxid = max(id) from tbl

create table #IDSeq
(
    id int
)

while @id < @maxid --whatever you max is
begin
    insert into #IDSeq values(@id)

    set @id = @id + 1
end

select TOP 1
    s.id 
from 
    #idseq s 
    left join tbl t on 
        s.id = t.id 
 where t.id is null

 drop table #IDSeq

Answer 3

you can get it by using min function and where not exists clause:

assuming your table is:

Table name: tbl
Number
-----
  1
  2
  3
  4
  6
  7

now you can say:

select (number+1) as gap
from tbl 
where number<(select max(number) from tbl)
and not exists (
          select  1
          from    tbl t 
          where   t.number = tbl.number + 1
        )
order by gap

Output:

gap
5

Answer 4

First of all you need to know there is no reason to find a gap of IDENTITY column and create a new record with that

But if you just want to know the answer you can try this:

WITH C(id) AS(
    SELECT 1
    UNION
    SELECT 2
    UNION
    SELECT 3
    UNION
    SELECT 4
    UNION
    SELECT 6
    UNION
    SELECT 7
    UNION
    SELECT 8
)
SELECT TOP 1 id + 1
FROM C m
WHERE NOT EXISTS
        (
        SELECT NULL
        FROM C mm 
        WHERE mm.id = m.id + 1
        )
ORDER BY id