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
Old Hand
Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)

Group: General Forum Members
Points: 315 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
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4481 Visits: 7406
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
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: 14161 Visits: 5555
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
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4481 Visits: 7406
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
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: 14161 Visits: 5555
Oh Oh.. thats what..i was scratching my head to find this out.. Thanks Peter :-)
WayneS
WayneS
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33296 Visits: 10702
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
Old Hand
Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)

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

Thank u very much..

Raj
Paul White
Paul White
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54710 Visits: 11391

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
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6390 Visits: 5209
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
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4481 Visits: 7406
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