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.
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:300223003030031300323003330034300353003630037300383003930040300433004930074Example output:30030, 10Please note that 30040 will not be in the output, because it is not consecutive from 30040-30049 inclusive.Other outputs might say:34300, 100or30030, 1034300, 10042000, 1000Thanks for your help,--Shaun--SMerrillSeattle, 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, 10slow down to move faster... |
|
|
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 valuesdeclare @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 thisinsert into @tab (val) select 30049 insert into @tab (val) select 30074 --then I declare a temp table and some variablesdeclare @consec table (val int, lastval int, consecutive int, concount int)insert into @consec(val) select val from @tab order by valdeclare @consecutive intdeclare @lastval intdeclare @groupcount intdeclare @groupval intset @consecutive = 0set @lastval = 0set @groupcount = 0set @groupval = 0--then the bit that worksupdate @consecset @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 endselect lastval, max(concount) as groupsizefrom @consecgroup by lastvalThis returns :groupstart, groupsize---------------------30022 130030 1130043 130048 230074 1and 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 aselect * 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...EnjoyPS - 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" |
|
|
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} |
|
|
|
|
|
|
|