Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Missing numbers in a series Expand / Collapse
Author
Message
Posted Wednesday, April 28, 2010 6:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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..!!
Post #911849
Posted Wednesday, April 28, 2010 6:46 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:59 PM
Points: 1,602, Visits: 6,679
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)

Post #911868
Posted Wednesday, April 28, 2010 6:57 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 5:03 PM
Points: 2,262, Visits: 5,427
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??
Post #911890
Posted Wednesday, April 28, 2010 7:01 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:59 PM
Points: 1,602, Visits: 6,679
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
Post #911898
Posted Wednesday, April 28, 2010 7:05 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 5:03 PM
Points: 2,262, Visits: 5,427
Oh Oh.. thats what..i was scratching my head to find this out.. Thanks Peter
Post #911903
Posted Wednesday, April 28, 2010 9:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:28 AM
Points: 5,370, Visits: 9,012
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
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,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #912103
Posted Thursday, April 29, 2010 12:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Post #912570
Posted Thursday, April 29, 2010 1:42 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:15 AM
Points: 9,928, Visits: 11,207
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #912595
Posted Thursday, April 29, 2010 2:04 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Saturday, November 29, 2014 3:55 PM
Points: 3,244, Visits: 5,010
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

Post #912604
Posted Thursday, April 29, 2010 2:19 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:59 PM
Points: 1,602, Visits: 6,679
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
Post #912610
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse