April 28, 2010 at 6:32 am
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..!!
April 28, 2010 at 6:46 am
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)
April 28, 2010 at 6:57 am
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??
April 28, 2010 at 7:01 am
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
April 28, 2010 at 7:05 am
Oh Oh.. thats what..i was scratching my head to find this out.. Thanks Peter 🙂
April 28, 2010 at 9:24 am
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
April 29, 2010 at 12:43 am
Im searching for the solution from last few days..
Thank u very much..
Raj
April 29, 2010 at 1:42 am
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
SQLkiwi blog
@SQL_Kiwi
April 29, 2010 at 2:04 am
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
April 29, 2010 at 2:19 am
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
April 29, 2010 at 3:05 am
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)
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 29, 2010 at 3:12 am
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
April 29, 2010 at 3:30 am
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.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 29, 2010 at 11:03 pm
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.
April 30, 2010 at 12:05 am
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.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 61 total)
You must be logged in to reply to this topic. Login to reply