August 25, 2010 at 10:51 am
Hi,
Can anybody help me on this.
If I have given few numbers like 1,2,3,4,6,7,9,11
I need to get number which missed the sequence. Output with this example is 5,8,10.
Is it possible to do it with single select statement.. Please provide different alternatives.
Regards
Ram
🙂
August 25, 2010 at 10:59 am
The preferred way would be to use a tally table.
; WITH
t1 AS (SELECT 1 N UNION ALL SELECT 1 N), -- 4
t2 AS (SELECT 1 N FROM t1 x, t1 y), -- 16
t3 AS (SELECT 1 N FROM t2 x, t2 y), -- 256
t4 AS (SELECT 1 N FROM t3 x, t3 y), -- 65536
Tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N
FROM t4 x, t4 y)
SELECT * FROM Tally LEFT JOIN SequenceTable ON Tally.N = SequenceTable.N
WHERE SequenceTable.N IS NULL
AND Tally.N BETWEEN (SELECT MIN(N) FROM SequenceTable)
AND (SELECT MAX(N) FROM SequenceTable)
August 25, 2010 at 11:03 am
you need to do two things;
you'll need a Tally Table so you have all the possible values for your comparison;
second , you have to get your data into a table, so it can be compared source.row against Tally.N;
one of the many split function contributions from the script resources here on the site can do that if your data is a string like '1,2,4 etc'
here is just one example for you to look at:
--Your Sample Table :
CREATE TABLE ExistingBarCodes(SomeStuff varchar(30), Barcode int )
Insert into ExistingBarCodes(Barcode)
SELECT 100
UNION SELECT 101
UNION SELECT 102
UNION SELECT 103
UNION SELECT 104
UNION SELECT 105
UNION SELECT 110
UNION SELECT 111
UNION SELECT 112
UNION SELECT 113
UNION SELECT 120
UNION SELECT 121
--copied shamelessly from a Jeff Moden Example
--Now, before we get to the solution, we need to make a well indexed table of sequential numbers. These "Tally" or "Numbers" tables are very powerful and can help do things in SQL Server 2000 as if we were using ROWNUM from SQL Server 2005. You should make a permanent Tally table as follows... yes, this is part of the solution for this and many other "impossible" tasks...
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--===== Now find the unused bar codes
SELECT Tally.N as AvailableBarCode from Tally
left outer join ExistingBarCodes on Tally.N = ExistingBarCodes.Barcode
WHERE ExistingBarCodes.Barcode IS NULL
--===== only numbers between a range?
SELECT Tally.N as AvailableBarCode from Tally
left outer join ExistingBarCodes on Tally.N = ExistingBarCodes.Barcode
WHERE ExistingBarCodes.Barcode IS NULL
AND Tally.N between 101 and 125
Lowell
August 25, 2010 at 3:55 pm
DECLARE@Sample TABLE
(
ID INT NOT NULL
)
INSERT@Sample
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 9 UNION ALL
SELECT11
-- Get the missing ID's here
SELECTID + 1 AS MissingID
FROM@Sample
EXCEPT
SELECTID
FROM@Sample
N 56°04'39.16"
E 12°55'05.25"
August 25, 2010 at 4:22 pm
SwePeso (8/25/2010)
DECLARE@Sample TABLE
(
ID INT NOT NULL
)
INSERT@Sample
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 9 UNION ALL
SELECT11
-- Get the missing ID's here
SELECTID + 1 AS MissingID
FROM@Sample
EXCEPT
SELECTID
FROM@Sample
This is not a complete solution. It SEEMS to work here, because all of the gaps in your sample data are one ID. If you had gaps larger than one ID, this would only give the first one in the gap. For a complete solution, you need to use the tally table as others have suggested.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 25, 2010 at 4:58 pm
Thanks for all replies..
I have used following function and select statement . Please clarify me if i am wrong.
ALTER FUNCTION MISSING_SEQ (@RANGE VARCHAR(8000)) RETURNS @RANGE_TAB TABLE (VALUE bigint)
AS
BEGIN
DECLARE @a VARCHAR(10)
DECLARE @TEMP TABLE (VALUE bigint)
DECLARE @TEMP1 TABLE (VALUE bigint)
DECLARE @max-2 INT
DECLARE @min-2 INT
WHILE CHARINDEX(',',@RANGE) >0
BEGIN
SET @a = LEFT(@RANGE,CHARINDEX(',',@RANGE)-1)
INSERT INTO @TEMP VALUES (@A)
SET @RANGE = RIGHT(@RANGE,LEN(@RANGE)-CHARINDEX(',',@RANGE))
END
INSERT INTO @TEMP VALUES (@RANGE)
SELECT @max-2 = MAX(VALUE) FROM @TEMP
SELECT @min-2 = MIN(VALUE) FROM @TEMP
BEGIN
INSERT INTO @TEMP1 VALUES (@MIN)
END
INSERT INTO @RANGE_TAB SELECT VALUE FROM @TEMP1 WHERE VALUE NOT IN (SELECT VALUE FROM @TEMP)
RETURN
END
SELECT * FROM DBO.MISSING_SEQ('1,2,3,5,6,8,10,50')
Regards
Ram
🙂
August 25, 2010 at 9:30 pm
Ram,
First of all, I'm opposed to any code that contains the keyword "WHILE". This means that you are processing things in a loop, instead of a set-based manner.
I would start by using one of the very high-speed delimited string split functions available. As of this time, this is the best (IMHO) Delimited Split Function.
I would then utilize this code:
with CTE AS
(
SELECT *
FROM dbo.DelimitedSplit8K('1,2,3,5,6,8,10,50', ',')
)
SELECT t.N
FROM dbo.Tally t
LEFT JOIN CTE
ON CTE.Item = t.N
WHERE t.N <= (SELECT max(convert(int, Item)) FROM CTE)
AND CTE.Item IS NULL
Edit: See this article[/url] on how to create a TALLY table, and how it can replace loops.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 26, 2010 at 7:47 am
Didn't you ask if it could be done in one SELECT statement? I agree that I would stay away for the WHILE statement if for no other reason than because it's just bad T-SQL. I'd definitely take a look at the string split function, that's what I use. I have a database that is solely for functions and stored procedures that are used globally (most of which are string and date functions). My string split function is stored there. I can it all the time. I also have my stored tally table there. Again, it is referenced often. I suggest you do something similar.
I'd take a good look at the tally table code if I were you. You're function may work fine for your needs - maybe it completes in a split second and you don't notice any problem whatsoever. And you may never. But the real problem is the logic; it is procedural. Your thinking needs to be switched a little so that when you see a problem like this you don't think WHILE loop, you think tally table. You're not going to get there unless you start somewhere. Make a tally table. Try to use it. Once you do, you'll find it so useful you'll wonder how you ever did anything with out it.
August 26, 2010 at 1:22 pm
A variation which performs the split on the fly
WITH cte1 AS
(
SELECT '1,2,3,5,6,8,10,50' AS InputString
)
,
cte2 AS
(
SELECT CONVERT(INT, Value) AS Value FROM cte1
CROSS APPLY
(
SELECT SUBSTRING(InputString + ',', N, CHARINDEX(',', InputString + ',', N) - N) AS Value
FROM Tally
WHERE N < LEN(InputString) + 2 AND SUBSTRING(',' + InputString + ',', N, 1) = ','
) AS Z
)
SELECT N FROM Tally WHERE N BETWEEN (SELECT MIN(Value) FROM cte2) AND (SELECT MAX(Value) FROM cte2)
EXCEPT
SELECT Value FROM cte2
August 26, 2010 at 4:41 pm
drew.allen (8/25/2010)
This is not a complete solution. It SEEMS to work here, because all of the gaps in your sample data are one ID. If you had gaps larger than one ID, this would only give the first one in the gap. For a complete solution, you need to use the tally table as others have suggested.Drew
So, do you have a Tally table that goes from -2,147,483,648 to 2,147,483,647 because that's the only way the proposed Tally table methods are going to provide a supposedly "complete" solution for this problem. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
August 26, 2010 at 4:44 pm
Ram:) (8/25/2010)
Thanks for all replies..I have used following function and select statement . Please clarify me if i am wrong.
Ok, it's wrong because it uses a WHILE loop.
It's also wrong because if you pass the values 1,2000000000, it's going to return every bloody number between 1 and 2 billion non-inclusive. Is that what you really want?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 26, 2010 at 4:54 pm
jvanderberg (8/26/2010)
Didn't you ask if it could be done in one SELECT statement?
Who? The OP? I don't see that anywhere in the original or subsequent posts by the OP.
Still, I agree... loops are bad here because they are RBAR here.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 26, 2010 at 5:50 pm
Jeff Moden (8/26/2010)
jvanderberg (8/26/2010)
Didn't you ask if it could be done in one SELECT statement?Who? The OP? I don't see that anywhere in the original or subsequent posts by the OP.
Still, I agree... loops are bad here because they are RBAR here.
@jeff - look at the very first post:
Is it possible to do it with single select statement.. Please provide different alternatives.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 26, 2010 at 5:52 pm
Jeff Moden (8/26/2010)
drew.allen (8/25/2010)
This is not a complete solution. It SEEMS to work here, because all of the gaps in your sample data are one ID. If you had gaps larger than one ID, this would only give the first one in the gap. For a complete solution, you need to use the tally table as others have suggested.Drew
So, do you have a Tally table that goes from -2,147,483,648 to 2,147,483,647 because that's the only way the proposed Tally table methods are going to provide a supposedly "complete" solution for this problem. 😉
ALTER TABLE dbo.TALLY ALTER COLUMN N bigint
now, you need a MUCH bigger range.
But the point is - you need to base your solution on what you'll be working with. Tally tables of just 10,000 rows may be completely adequate for your use - but only you will know that.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 26, 2010 at 11:09 pm
WayneS (8/26/2010)
Jeff Moden (8/26/2010)
jvanderberg (8/26/2010)
Didn't you ask if it could be done in one SELECT statement?Who? The OP? I don't see that anywhere in the original or subsequent posts by the OP.
Still, I agree... loops are bad here because they are RBAR here.
@jeff - look at the very first post:
Is it possible to do it with single select statement.. Please provide different alternatives.
Ah... if it were a snake... thanks, Wayne.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply