Need Assistance for Groups and Islands Problem

Question

Post reply

Need Assistance for Groups and Islands Problem

vignesh.ms

Hall of Fame

Points: 3456
More actions
April 30, 2014 at 5:55 am

#286332

create table #sample (rowguid int identity ,id_frm int ,id_to int)
insert into #sample values( 1,5)
insert into #sample values( 7,13)
insert into #sample values( 17,20)
In the above table I have values starting Id and Ending Id. I need to prepare a table which has all the numbers falls between starting Id and Ending Id
i have tried it with looping but response is very slow in real world.
any body help me with query ???
This is what I have tried so far...
declare @sql varchar(8000) = '
select top '+cast((select max(id_to) from #sample) as varchar(100))+' identity(int,1,1) as guidid into tally from sysobjects,syscolumns '
exec (@sql)
alter table Tally add slab varchar(10)
create clustered index idx on Tally(guidid)
create clustered index idx on #sample(id_frm asc,id_to desc)
update Tally set slab = rowguid
from #sample join Tally on guidid between id_frm and id_to
delete from Tally where slab is null
select * from Tally
This query works fine with small numbers
But My real time table have 13 digit nos. It through Arithmetic overflow error

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

vignesh.ms (4/30/2014)
...
But My real time table have 13 digit nos. It through Arithmetic overflow error

Please provide some realistic sample data so that folks attempting to help won't have the same problem.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

gbritton1 SSCertifiable Points: 6522 More actions · Answer 2

Use bigint. And you don't need the dynamic SQL:

create table #sample (rowguid bigint identity ,id_frm bigint ,id_to bigint)

insert into #sample values( 1,5)

insert into #sample values( 7,13)

insert into #sample values( 17,20)

select top((select max(id_to) from #sample)) identity(bigint,1,1) as guidid into tally from sysobjects,syscolumns

vignesh.ms Hall of Fame Points: 3456 More actions · Answer 3

ChrisM@Work (4/30/2014)
Please provide some realistic sample data so that folks attempting to help won't have the same problem.

Table structure is same... Data only differs

It will always starts and ends with 13 digit number ...

Here are the sample records to insert

insert into #sample values( 1254858693000,1254858693999)

insert into #sample values( 9867668925500,9867668929999)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

DROP table #sample

create table #sample (rowguid int identity ,id_frm int ,id_to int)

insert into #sample values( 1,5)

insert into #sample values( 7,13)

insert into #sample values( 17,20)

insert into #sample values( 30,1962)

SELECT

id_frm+n, -- n is BIGINT, result is BIGINT

*

FROM #sample

CROSS APPLY (

SELECT TOP(id_to-id_frm+1)

n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1

FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a (n), -- This iTally provides

(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b (n), -- 10000 rows MAX.

(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c (n), -- expand as necessary

(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)

) iTally (n)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

gbritton1 SSCertifiable Points: 6522 More actions · Answer 5

I just changed your table slightly:

create table #sample (rowguid int identity ,id_frm bigint ,id_to bigint)

using bigint instead of int. With that in place, the rest of your queries work fine for me.