CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
alejandro carnero
alejandro carnero

Reputation: 1784

Find missing numbers in a column

I have this column in T-SQL:

 1
 2
 3
 7
 10

have SQl a function for detect the missing numbers in the sequence 4,5,6 and 8,9 I have try something like if ( a-b >1 ) then we have a missing number

with coalesce but i dont understand . Thanks by any orientation

Upvotes: 1

Views: 2511

Answers (3)

John Odom
John Odom

Reputation: 1213

I know this is a late answer, but here is a query that uses recursive table expressions to get the missing values between the minimum and maximum values in a table:

WITH CTE AS
(
    --This is called once to get the minimum and maximum values
    SELECT nMin = MIN(t.ID), MAX(t.ID) as 'nMax' 
    FROM Test t
    UNION ALL
    --This is called multiple times until the condition is met
    SELECT nMin + 1, nMax 
    FROM CTE
    WHERE nMin < nMax
)

--Retrieves all the missing values in the table.
SELECT c.nMin
FROM CTE c
WHERE NOT EXISTS
(
    SELECT ID
    FROM Test
    WHERE c.nMin = ID
)

This was tested with the following schema:

CREATE TABLE Test
(
    ID int NOT NULL
)

INSERT INTO Test
    Values(1)

INSERT INTO Test
    Values(2)

INSERT INTO Test
    Values(3)

INSERT INTO Test
    Values(7)

INSERT INTO Test
    Values(10)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

The following query will identify where each sequence starts and the number that are missing:

select t.col + 1 as MissingStart, (nextval - col - 1) as MissingSequenceLength
from (select t.col,
             (select min(t.col) from t t2 where t2.col > t.col) as nextval
      from t
     ) t
where nextval - col > 1

This is using a correlated subquery to get the next value in the table.

Upvotes: 1

www
www

Reputation: 4391

You can try this:

DELCARE @a
SET @a = SELECT MIN(number) FROM table
WHILE (SELECT MAX(number) FROM table ) > @a
BEGIN
IF @a NOT IN ( SELECT number FROM table )
PRINT @a
SET @a=@a+1
END

Upvotes: 3

Related Questions