Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Missing numbers in a series


Missing numbers in a series

Author
Message
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
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
SQLblog.com
@SQL_Kiwi
Atif-ullah Sheikh
Atif-ullah Sheikh
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3280 Visits: 5158
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


Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
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
SQLblog.com
@SQL_Kiwi
Atif-ullah Sheikh
Atif-ullah Sheikh
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3280 Visits: 5158

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.

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

Sometimes, winning is not an issue but trying.

You can check my BLOG here


Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
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.:-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
SQLblog.com
@SQL_Kiwi
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4558 Visits: 9485
-- 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


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
gurukiran.bhat
gurukiran.bhat
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44962 Visits: 39862
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44962 Visits: 39862
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44962 Visits: 39862
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
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