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 Thursday, April 29, 2010 3:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:58 AM
Points: 11,192, Visits: 11,097
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #912624
Posted Thursday, April 29, 2010 3:12 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: Friday, June 27, 2014 8:02 AM
Points: 3,241, Visits: 4,996
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


----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here

Post #912627
Posted Thursday, April 29, 2010 3:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:58 AM
Points: 11,192, Visits: 11,097
Atif Sheikh (4/29/2010)
Sorry. forgot to replace the table names in the cte.

You changed rather more than that!
"Forgot" must remember that one. Funny.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #912638
Posted Thursday, April 29, 2010 11:03 PM


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: Friday, June 27, 2014 8:02 AM
Points: 3,241, Visits: 4,996

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..


----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here

Post #913408
Posted Friday, April 30, 2010 12:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:58 AM
Points: 11,192, Visits: 11,097
Atif Sheikh (4/29/2010)
[quote]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..

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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #913449
Posted Friday, April 30, 2010 8:33 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 9:00 AM
Points: 1,595, Visits: 4,585
-- create and insert a table variable containing missing numbers.
declare @missingnumbers table (n int not null primary key);
insert into @missingnumbers
select 1 union select 2 union select 4 union select 5 union
select 7 union select 8 union select 11 union select 12 union
select 13 union select 15 union select 17 union select 19 union select 20;
-- create and insert a table variable containing all numbers
-- between min(n) and max(n).
declare @allnumbers table (n int not null primary key);
declare @n int, @nmax int;
select @n = min(n), @nmax = max(n) from @missingnumbers;
while @n < @nmax
begin
select @n = @n + 1;
insert into @allnumbers (n) values (@n);
end;
-- left join the 2 tables and return those numbers
-- not contained in the @missingnumbers table.
select a.n
from @allnumbers a
left join @missingnumbers m on m.n = a.n
where m.n is null;

n
-----------
3
6
9
10
14
16
18
Post #913756
Posted Monday, May 3, 2010 2:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 15, 2012 6:15 AM
Points: 10, Visits: 129
following method worked out for me

for this example i have used table called table1 which has single column column1.

create table table1
(column1 int)

I have inserted values 1,2,3,5,9 in to this table using following query

insert into table1
select 1 union all
select 2 union all
select 3 union all
select 5 union all
select 9 union all
select 10

In the above sequence 0,4,6,7,8 are missing. to find out missed values i have used following method

1. create a temporary table temp_table(later we can drop it)
create table temp_table
(column2 int)

2. following query will insert all the values from 0 to 10 into temp_table

DECLARE @val1 int;
set @val1 = 0;
while (@val1 < (select max(column1) from table1))
begin
insert into temp_table
select @val1
set @val1 = @val1+1
end


3. use exept function of SQl server 2005 to get missed values
select * from temp_table
except
select * from table1

4.delete temp_table
Post #914528
Posted Monday, May 3, 2010 7:43 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:52 PM
Points: 36,773, Visits: 31,229
gurukiran.bhat (5/3/2010)
following method worked out for me

for this example i have used table called table1 which has single column column1.

create table table1
(column1 int)

I have inserted values 1,2,3,5,9 in to this table using following query

insert into table1
select 1 union all
select 2 union all
select 3 union all
select 5 union all
select 9 union all
select 10

In the above sequence 0,4,6,7,8 are missing. to find out missed values i have used following method

1. create a temporary table temp_table(later we can drop it)
create table temp_table
(column2 int)

2. following query will insert all the values from 0 to 10 into temp_table

DECLARE @val1 int;
set @val1 = 0;
while (@val1 < (select max(column1) from table1))
begin
insert into temp_table
select @val1
set @val1 = @val1+1
end


3. use exept function of SQl server 2005 to get missed values
select * from temp_table
except
select * from table1

4.delete temp_table


Yep... that works for gaps of ten rows. Try it on a gap that jumps from 1000000 to 2000000 because some manager decided to use ranges of numbers to isolate customers from different countries.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #914682
Posted Monday, May 3, 2010 7:45 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:52 PM
Points: 36,773, Visits: 31,229
Folks, I think that just about anything with any form of a Tally table is the wrong way to do it here especially if you have very large gaps for the reasons like the one in my response in the post above. I've got a very old but fast method of doing this and I'll try to remember to post it tonight after work...

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #914684
Posted Monday, May 3, 2010 5:38 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:52 PM
Points: 36,773, Visits: 31,229
Ok... here's a test table with some huge gaps over a huge range of numbers along with some single row gaps. The code takes about 12 seconds to run on my 8 year old machine. Details are in the comments, as usual...
DROP TABLE #MyTest
GO
--===== Create and populate a 2,000,000 row test table.
-- This first SELECT creates a range of 1 to 1,000,000 unique numbers starting at 10,000,001
SELECT TOP 1000000
MyID = ISNULL(CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + 10000000 AS BIGINT),0)
INTO #MyTest
FROM Master.sys.All_Columns ac1,
Master.sys.All_Columns ac2
-- This second SELECT creates a range of 1 to 1,000,000 unique numbers starting at 82,011,000,000,001
UNION ALL
SELECT TOP 1000000
MyID = ISNULL(CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + 82011000000000 AS BIGINT),0)
FROM Master.sys.All_Columns ac1,
Master.sys.All_Columns ac2

--===== Create the quintessential Primary Key
-- Takes about 3 seconds to execute.
ALTER TABLE #MyTest
ADD PRIMARY KEY CLUSTERED (MyID)

--===== Delete some know rows to demo the gap detection code
-- This deletes 50 rows spaced 2000 apart in the given range
-- to demo small gaps
DELETE #MyTest
WHERE MyID BETWEEN 82011000400001 AND 82011000500000
AND MyID %2000 = 0

-- This deletes 100,000 rows in a given range to demo large gaps
DELETE #MyTest
WHERE MyID BETWEEN 82011000600001 AND 82011000700000


Here's the gap detection code I spoke of. It takes about 3 seconds to run on that same 8 year old machine. I'm thinking that because of the very large ranges of numbers that a Tally table just isn't going to cut it for stuff like this...

--===== Find the "gap ranges" --This takes 3 seconds on my 8 year old machine
-- Finds trailing edge of "islands" and then computes the gaps
-- This assumes that gaps include any whole number greater than 0
SELECT GapStart = (SELECT ISNULL(MAX(lo.MyID),0)+1
FROM #MyTest lo
WHERE lo.MyID < hi.MyID),
GapEnd = hi.MyID - 1
FROM #MyTest hi
WHERE hi.MyID NOT IN (SELECT MyID + 1 FROM #MyTest)




--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #915010
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse