

SSC Rookie
Group: General Forum Members
Last Login: Tuesday, October 12, 2010 4:24 AM
Points: 41,
Visits: 102


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..!!




SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 11:25 PM
Points: 1,619,
Visits: 6,882


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)




SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 7:04 PM
Points: 2,263,
Visits: 5,500


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??




SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 11:25 PM
Points: 1,619,
Visits: 6,882


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




SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 7:04 PM
Points: 2,263,
Visits: 5,500


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




SSCertifiable
Group: General Forum Members
Last Login: Today @ 7:45 AM
Points: 5,756,
Visits: 9,961


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 commadelimited 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/TSQL/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)N1))) 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 TSQL 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, How to ask a question, Performance Problems, Common date/time routines, CROSSTABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings




SSC Rookie
Group: General Forum Members
Last Login: Tuesday, October 12, 2010 4:24 AM
Points: 41,
Visits: 102


Im searching for the solution from last few days..
Thank u very much..
Raj




SSCrazy Eights
Group: General Forum Members
Last Login: Tuesday, February 2, 2016 4:47 AM
Points: 9,932,
Visits: 11,308


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
Paul White SQLPerformance.com SQLblog.com @SQL_Kiwi




Hall of Fame
Group: General Forum Members
Last Login: Yesterday @ 6:24 AM
Points: 3,244,
Visits: 5,086


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 here




SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 11:25 PM
Points: 1,619,
Visits: 6,882


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



