SQL Server: How do I get the highest value not set of an int column?

Question

Let's take an example. These are the rows of the table I want get the data:

The column I'm talking about is the reference one. The user can set this value on the web form, but the system I'm developing must suggest the lowest reference value still not used.

As you can see, the smallest value of this column is 35. I could just take the smaller reference and sum 1, but, in that case, the value 36 is already used. So, the value I want is 37.

Is there a way to do this without a loop verification? This table will grow so much.

Answer 1

This is for 2012+

DECLARE @Tbl TABLE (id int, reference int)
INSERT INTO @Tbl
        ( id, reference )
VALUES  
(1, 49),
(2, 125),
(3, 35),
(4, 1345),
(5, 36),
(6, 37)


SELECT
    MIN(A.reference) + 1 Result
FROM
(
    SELECT
        *,
        LEAD(reference) OVER (ORDER BY reference) Tmp
    FROM
        @Tbl
) A
WHERE
    A.reference - A.Tmp != -1

Result: 37

Answer 2

Here is yet another place where the tally table is going to prove invaluable. In fact it is so useful I keep a view on my system that looks like this.

create View [dbo].[cteTally] as

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a cross join E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a cross join E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )
select N from cteTally

Next of course we need some sample data and table to hold it.

create table #Something
(
    id int identity
    , reference int
    , description varchar(10)
)

insert #Something (reference, description)
values (49, 'data1')
    , (125, 'data2')
    , (35, 'data3')
    , (1345, 'data4')
    , (36, 'data5')
    , (7784, 'data6')

Now comes the magic of the tally table.

select top 1 t.N
from cteTally t
left join  #Something s on t.N = s.reference
where t.N >= (select MIN(reference) from #Something)
    and s.id is null
order by t.N

Answer 3

This is ugly, but should get the job done:

select
  top 1 reference+1
from
  [table]
where
  reference+1 not in (select reference from [table])
order by reference

Answer 4

I used a table valued express to get the next value. I first left outer joined the table to itself (shifting the key in the join by +1). I then looked only at rows that had no corresponding match (b.ID is null). The minimum a.ReferenceID + 1 gives us the answer we are looking for.

create table MyTable
( 
  ID int identity,
  Reference int,
  Description varchar(20)
)

insert into MyTable values (10,'Data')
insert into MyTable values (11,'Data')
insert into MyTable values (12,'Data')
insert into MyTable values (15,'Data')

-- Find gap
;with Gaps as
(
  select a.Reference+1 as 'GapID'
    from MyTable a
    left join MyTable b on a.Reference = b.Reference-1
    where b.ID is null
)
select min(GapID) as 'NewReference'
  from Gaps

NewReference
------------
13

I hope the code was clearer than my description.

Answer 5

CREATE TABLE #T(ID INT , REFERENCE INT, [DESCRIPTION] VARCHAR(50))

INSERT INTO #T

SELECT 1,49  , 'data1'  UNION ALL
SELECT 2,125 , 'data2'  UNION ALL
SELECT 3,35  , 'data3'  UNION ALL
SELECT 4,1345, 'data4'  UNION ALL
SELECT 5,36  , 'data5'  UNION ALL
SELECT 6,7784, 'data6'  

SELECT TOP 1 REFERENCE + 1
FROM #T T1
WHERE
NOT EXISTS
(
  SELECT 1 FROM #T T2 WHERE T2.REFERENCE = T1.REFERENCE + 1 
)
ORDER BY T1.REFERENCE

--- OR 

SELECT MIN(REFERENCE) + 1
FROM #T T1
WHERE
NOT EXISTS
(
  SELECT 1 FROM #T T2 WHERE T2.REFERENCE = T1.REFERENCE + 1 
)

Answer 6

How about using a Tally table. The following illustrates the concept. It would be better to use a persisted numbers table as opposed to the cte however the code below illustrates the concept.

For further reading as to why you should use a persisted table, check out the following link: sql-auxiliary-table-of-numbers

DECLARE @START int = 1, @END int = 1000

CREATE TABLE #TEST(UsedValues INT)
INSERT INTO #TEST(UsedValues) VALUES
(1),(3),(5),(7),(9),(11),(13),(15),(17)

;With NumberSequence( Number ) as
(
    Select @start as Number
        union all
    Select Number + 1
        from NumberSequence
        where Number < @end
)

SELECT MIN(Number)
FROM NumberSequence n
LEFT JOIN #TEST t
    ON n.Number = t.UsedValues
WHERE UsedValues IS NULL
OPTION ( MAXRECURSION 1000 )

Answer 7

You could try using a descending order:

SELECT DISTINCT reference
FROM `Resultsados`
ORDER BY `reference` ASC;

As far as I know, there is no way to do this without a loop. To prevent multiple values from returning be sure to use DISTINCT.