Missing numbers in a series

  • I have the data like 1,2,4,5,7,8,11,12,13,15,17,19,20. How to find the missing numbers in the series?

    Please help me

    Thanks in advance..!!

  • I'm not sure if this fulfills your requirements, but you can use a tally or numbers table to find the missing numbers:

    DECLARE @MissingNumbers TABLE (N INT)

    INSERT INTO @MissingNumbers

    VALUES (1),(2),(4),(5),(7),(8),(11),(12),(13),(15),(17),(19),(20)

    ;WITH Tally(N) AS

    (

    SELECT number N FROM master.dbo.spt_values WHERE Type = 'P' AND number > 0

    )

    SELECT

    T.N

    FROM

    Tally T

    LEFT JOIN

    @MissingNumbers MN ON MN.N = T.N

    WHERE

    MN.N IS NULL

    AND T.N <= (SELECT MAX(N) FROM @MissingNumbers)

  • Peter, i have got one doubt actually.. not related to the OP's request, but general doubt..

    in your code , you have given the INSERT statement as

    INSERT INTO @MissingNumbers

    VALUES (1),(2),(4),(5),(7),(8),(11),(12),(13),(15),(17),(19),(20)

    When i tried running it, it said there was syntax error near this..I, infact, seen this code, in many more place.. For INSERT statements, all i use till date was like INSERT INTO TABLE SELECT UNION ALL SELECT or INSERT INTO TABLE VALUES INSERT INTO TABLE VALUES..

    Am i missing something in SSMS? How did this code work for you? Are there any special settings tat need to be done for using INSERT statement like that??

  • As this is a SQL2K8 forum, I used the new SQL2K8 syntax for inserting multiple values. If you are using SQL2K5 then you should use UNION ALL indeed.

    Peter

  • Oh Oh.. thats what..i was scratching my head to find this out.. Thanks Peter 🙂

  • Raj.. (4/28/2010)


    I have the data like 1,2,4,5,7,8,11,12,13,15,17,19,20. How to find the missing numbers in the series?

    Please help me

    Thanks in advance..!!

    Raj,

    Since you are showing a comma-delimited list, this solution will first break that list apart, and then show the missing numbers in this list. It will run faster if you have your own Tally table - see the article mentioned below for how to build your own.

    declare @list varchar(50),

    @Delimiter char(1);

    set @list = '1,2,4,5,7,8,11,12,13,15,17,19,20';

    set @Delimiter = ',';

    -- first, need to break down into separate items.

    -- See Jeff Moden's article The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

    -- at http://www.sqlservercentral.com/articles/T-SQL/62867/ for how a tally table can split strings apart.

    WITH Tens (N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ),

    Thousands (N) AS (SELECT 1 FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3),

    Millions (N) AS (select 1 FROM Thousands t1 CROSS JOIN Thousands t2),

    Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions),

    ItemSplit (ItemOrder, Item) AS (

    SELECT N,

    RTRIM(LTRIM(SUBSTRING(@Delimiter + @list + @Delimiter,N+1,CHARINDEX(',',@Delimiter + @list + @Delimiter,N+1)-N-1)))

    FROM Tally

    WHERE N < LEN(@Delimiter + @list + @Delimiter)

    AND SUBSTRING(@Delimiter + @list + @Delimiter,N,1) = ','

    )

    SELECT t.N

    FROM Tally t

    LEFT JOIN ItemSplit ON t.N = ItemSplit.Item

    WHERE ItemSplit.Item IS NULL

    AND t.N < (select MAX(convert(int,Item)) from ItemSplit)

    ORDER BY t.N

    Edit: corrected name misspelling

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Im searching for the solution from last few days..

    Thank u very much..

    Raj

  • DECLARE @list VARCHAR(50) = '1,2,4,5,7,8,11,12,13,15,17,19,20',

    @Delimiter CHAR(1) = ',';

    SELECT Numbers.n

    FROM dbo.Numbers(REVERSE(LEFT(REVERSE(@list), CHARINDEX(@Delimiter, REVERSE(@list)) - 1))) Numbers

    WHERE CHARINDEX(@Delimiter + CONVERT(VARCHAR(11), Numbers.n) + @Delimiter, @Delimiter + @list + @Delimiter) = 0;

    Uses my numbers table function:

    SET ANSI_NULLS ON

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE FUNCTION [dbo].[Numbers]

    (

    @Count BIGINT

    )

    RETURNS TABLE

    WITH SCHEMABINDING

    AS RETURN

    WITH

    E1 AS

    (

    SELECT n = 1 UNION ALL

    SELECT 2 UNION ALL

    SELECT 3 UNION ALL

    SELECT 4 UNION ALL

    SELECT 5 UNION ALL

    SELECT 6 UNION ALL

    SELECT 7 UNION ALL

    SELECT 8 UNION ALL

    SELECT 9 UNION ALL

    SELECT 10

    ), -- 1E1 rows

    E2 AS (SELECT X.n FROM E1 X CROSS JOIN E1), -- 1E2 rows

    E4 AS (SELECT X.n FROM E2 X CROSS JOIN E2), -- 1E4 rows

    E8 AS (SELECT X.n FROM E4 X CROSS JOIN E4), -- 1E8 rows

    Numbers AS (SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM E8)

    SELECT TOP (@Count)

    N.n

    FROM Numbers N

    ORDER BY

    N.n ASC;

    GO

  • Hi...

    DECLARE @MissingNumbers TABLE (N INT)

    Declare @vMax int

    INSERT INTO @MissingNumbers

    VALUES (1),(2),(4),(5),(7),(8),(11),(12),(13),(15),(17),(19),(20)

    Set @vMax = (Select MAX(N) from @MissingNumbers)

    ;with wcte as (

    Select Top(@vMax) ROW_NUMBER() over(order by a.N) NAll

    from @MissingNumbers a, @MissingNumbers b

    ) Select NAll from wcte a

    Left outer Join @MissingNumbers b on b.N = a.Nall

    where b.N is Null

    Atif SHeikh

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG
    [font="Arial Black"]here[/font][/url][/right]

  • Atif Sheikh (4/29/2010)


    Hi...

    DECLARE @MissingNumbers TABLE (N INT)

    Declare @vMax int

    INSERT INTO @MissingNumbers

    VALUES (1),(2),(4),(5),(7),(8),(11),(12),(13),(15),(17),(19),(20)

    Set @vMax = (Select MAX(N) from @MissingNumbers)

    ;with wcte as (

    Select Top(@vMax) ROW_NUMBER() over(order by a.N) NAll

    from @MissingNumbers a, @MissingNumbers b

    ) Select NAll from wcte a

    Left outer Join @MissingNumbers b on b.N = a.Nall

    where b.N is Null

    Atif SHeikh

    Sorry, but this does not work when the number of missing numbers is more then 50%. For example

    DECLARE @MissingNumbers TABLE (N INT)

    Declare @vMax int

    INSERT INTO @MissingNumbers

    VALUES (19),(20)

    Set @vMax = (Select MAX(N) from @MissingNumbers)

    ;with wcte as (

    Select Top(@vMax) ROW_NUMBER() over(order by a.N) NAll

    from @MissingNumbers a, @MissingNumbers b

    ) Select NAll from wcte a

    Left outer Join @MissingNumbers b on b.N = a.Nall

    where b.N is Null

    will result in

    1

    2

    3

    4

  • This doesn't really add much value to Peter's version, but just for my own amusement:

    DECLARE @MissingNumbers

    TABLE (n INTEGER NOT NULL);

    INSERT @MissingNumbers (n)

    VALUES (1),(2),(4),(5),(7),(8),(11),(12),(13),(15),(17),(19),(20);

    SELECT N.n

    FROM dbo.Numbers((SELECT MAX(n) FROM @MissingNumbers)) N

    EXCEPT SELECT n FROM @MissingNumbers;

    (Using my previously posted function)

  • Sorry. forgot to replace the table names in the cte.

    DECLARE @MissingNumbers TABLE (N INT)

    Declare @vMax int

    INSERT INTO @MissingNumbers

    VALUES (19),(20)

    Set @vMax = (Select MAX(N) from @MissingNumbers)

    ;with wcte as (

    Select Top(@vMax) ROW_NUMBER() over(order by a.Object_id,a.Column_ID) NAll

    from sys.columns a, sys.columns b

    ) Select NAll from wcte a

    Left outer Join @MissingNumbers b on b.N = a.Nall

    where b.N is Null

    Atif Sheikh

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG
    [font="Arial Black"]here[/font][/url][/right]

  • Atif Sheikh (4/29/2010)


    Sorry. forgot to replace the table names in the cte.

    You changed rather more than that!

    "Forgot" :laugh: must remember that one. Funny.

  • You changed rather more than that!

    "Forgot" must remember that one. Funny.

    Not much. Basically, concept was of tally table. I implemented with the memory table, which was supposed to be created by sys.columns or other table like that.:-D.

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG
    [font="Arial Black"]here[/font][/url][/right]

  • Atif Sheikh (4/29/2010)


    Not much. Basically, concept was of tally table. I implemented with the memory table, which was supposed to be created by sys.columns or other table like that.:-D.

    Yep I appreciate the intent - just pointing out that it was actually quite a big change.

    Peter was right to point out the limitations.

Viewing 15 posts - 1 through 15 (of 61 total)

You must be logged in to reply to this topic. Login to reply