SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Missing numbers in a series


Missing numbers in a series

Author
Message
Ujar
Ujar
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 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..!!
Peter Brinkhaus
Peter Brinkhaus
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2491 Visits: 7404
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)


ColdCoffee
ColdCoffee
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5807 Visits: 5553
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??
Peter Brinkhaus
Peter Brinkhaus
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2491 Visits: 7404
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
ColdCoffee
ColdCoffee
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5807 Visits: 5553
Oh Oh.. thats what..i was scratching my head to find this out.. Thanks Peter :-)
WayneS
WayneS
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14965 Visits: 10629
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, 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

Ujar
Ujar
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 102
Im searching for the solution from last few days..

Thank u very much..

Raj
Paul White
Paul White
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24910 Visits: 11359

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
Atif-ullah Sheikh
Atif-ullah Sheikh
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4446 Visits: 5202
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


Peter Brinkhaus
Peter Brinkhaus
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2491 Visits: 7404
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search