Find missing sequence number. Get the numbers where sequence is missing.

Most of the time the question was asked while having Database interview that we have number sequence present in a table but some numbers are missing. Can you write the query to detect where number is missing from sequence and if possible then also can get the missing number?

OK, it’s not a rocket science so let’s begin. Please give me a paper and a pen to write query Sir.

I am using a Temporary variable table here.

DECLARE @Temp TABLE

(

       Id INT IDENTITY(1,1),

       Num INT

)

INSERT INTO @Temp (Num) VALUES (1) ,(2), (4), (6), (7), (9)

SELECT * FROM @Temp

SELECT a.Id , a.Num, b.Num, (a.Num - b.Num) as Diff, (b.Num + 1) as MissingNum

FROM @Temp a

LEFT JOIN @Temp b ON b.Id = (a.Id - 1)

WHERE (a.Num - b.Num) > 1

You see it’s just a simple SELECT statement but tricky.

OK, yes it will produce my result but we do not have any Id field in our database, we only have one column Num. Can you write a query for same?

OK, sir give me few seconds to think…..

OK so there are two possible ways I will use. With CTE or without CTE.

Without CTE:

DECLARE @Temp TABLE

(

       Num INT

)

INSERT INTO @Temp (Num) VALUES (1) ,(2), (4), (6), (7), (9)

DECLARE @TempFinal TABLE

(

       Id INT IDENTITY(1,1),

       Num INT

)

INSERT INTO @TempFinal

       SELECT Num FROM @Temp

SELECT * FROM @TempFinal

SELECT a.Id , a.Num, b.Num, (a.Num - b.Num) as Diff, (b.Num + 1) as MissingNum

FROM @TempFinal a

LEFT JOIN @TempFinal b ON b.Id = (a.Id - 1)

WHERE (a.Num - b.Num) > 1

Sir, I have used one temporary variable table (@TempFinal) where I have added Id column that will generate the id for each row and rest query is again same as previous. Only I have to change the table name from @Temp to @TempFinal.

With CTE:

Now using CTE I do not have to create extra temporary table.

DECLARE @Temp TABLE

(

       Num INT      

)

INSERT INTO @Temp (Num) VALUES (1) ,(2), (4), (6), (7), (9)

;WITH CTE

AS

(

       SELECT ROW_NUMBER() OVER(ORDER BY Num) AS Id, Num FROM @Temp

)

SELECT a.Id , a.Num, b.Num, (a.Num - b.Num) AS Diff, (b.Num + 1) as MissingNum

FROM CTE a

JOIN CTE b ON b.Id = (a.Id - 1)

WHERE (a.Num - b.Num) > 1

OK, that’s what I am looking for.