Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Consecutive test without a Cursor?

Author  Topic 

SMerrill
Posting Yak Master

206 Posts

Posted - 2002-12-11 : 20:13:06
I know some of you are dead-set against cursors, so please see if you can help me with this one. (It is not homework.)

I have a very large table (730,000 rows). I need to determine if an integer in one field of the composite key in that table is consecutive for 10 rows, 100 rows, or 1000 rows. If it is consecutive for that many rows, I need to pick the topmost row value and display it in one row along with the denomination.
The values in this one column are always 5 digits long.

Example input:
30022
30030
30031
30032
30033
30034
30035
30036
30037
30038
30039
30040
30043
30049
30074

Example output:
30030, 10
Please note that 30040 will not be in the output, because it is not consecutive from 30040-30049 inclusive.

Other outputs might say:
34300, 100
or
30030, 10
34300, 100
42000, 1000

Thanks for your help,
--Shaun


--SMerrill
Seattle, WA

skillile
Posting Yak Master

208 Posts

Posted - 2002-12-11 : 23:03:24
I'm confused:

input:
30030
30031
30032
30033
30034
30035
30036
30037
30038
30039

if we are looking at 30, 31,32...39 wouldn't 30039 be your "topmost value"

output:

30039, 10



slow down to move faster...
Go to Top of Page

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-12-12 : 00:30:46
quote:

I'm confused:


I'm confused too - shouldn't the output be
30030, 11
? isn't 3040 consecutive after 3039??

Anyhow - here's what I think you want

--set up table with values
declare @tab table (val int)

insert into @tab (val) select 30022
insert into @tab (val) select 30030
insert into @tab (val) select 30031
insert into @tab (val) select 30032
insert into @tab (val) select 30033
insert into @tab (val) select 30034
insert into @tab (val) select 30035
insert into @tab (val) select 30036
insert into @tab (val) select 30037
insert into @tab (val) select 30038
insert into @tab (val) select 30039
insert into @tab (val) select 30040
insert into @tab (val) select 30043
insert into @tab (val) select 30048 --I added this
insert into @tab (val) select 30049
insert into @tab (val) select 30074

--then I declare a temp table and some variables
declare @consec table (val int, lastval int, consecutive int, concount int)
insert into @consec(val) select val from @tab order by val

declare @consecutive int
declare @lastval int
declare @groupcount int
declare @groupval int

set @consecutive = 0
set @lastval = 0
set @groupcount = 0
set @groupval = 0

--then the bit that works
update @consec
set @consecutive = consecutive = case when @lastval <> val - 1 then 1 else 0 end,
@lastval = val,
@groupcount = concount = case when @consecutive = 0 then @groupcount + 1 else 1 end,
@groupval = lastval = case when @consecutive = 0 then @groupval else val end

select lastval, max(concount) as groupsize
from @consec
group by lastval


This returns :
groupstart, groupsize
---------------------
30022 1
30030 11
30043 1
30048 2
30074 1

and I THINK that is what you want - but who knows. If you really do want to restart every 10/100 numbers then you can play with the logic which decides whether the number is consecutive.

What I'm basically doing is to create a "consec" table which holds the start of each group and the distance of each val from the start of the group. If you're not sure how it works then do a
select * from @consec to find out.

@consecutive holds a 1 when the val is 1 more than the previous val
@lastval holds the previous value in the table
@groupcount holds how many consecutive numbers we found so far
@groupval holds the val on which the current group is based (easier to remember it here than have to work it out later)

In theory, you have to have the order by (in red) - but in practice, when you build the temptable, it inserts the values ni order - so you could take it out of my code and the script will return the same values.

This should be faster than a cursor - but how it operates on a 730000 record table I couldn't say...

Enjoy

PS - I added 30048 for my own "worst case" testing.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"
Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-12-12 : 07:10:38
quote:
I need to determine if an integer in one field of the composite key in that table is consecutive for 10 rows, 100 rows, or 1000 rows.


Maybe this is poorly worded or maybe you are truly mis-informed; its hard to tell.

There is no such thing as 'consecutive rows' in relational theory. Codd's Guaranteed Access Rule states that data in a relational databases is accessible by resorting to a table name, primary key value and column name ... and nothing else. There is not 'next row' or 'previous row' ... there is nothing to say that the row '30031' is after '30030'.

If what you mean is consecutive values in your column, then rrb's solution is a sound one.

Jay White
{0}
Go to Top of Page
   

- Advertisement -