November 19, 2008 at 2:39 pm
SQL to pull consecutive numbers rows
Hello, I have a table with these sample recs.
ColA ColB ColC
00100 295.00 295.00
00100 295.01 295.01
00100 295.02 295.02
00100 295.03 295.03
00100 295.04 295.04
00100 295.05 295.05
00100 295.10 295.15
00100 295.98 295.98
What SQL query (prefers not to use cursor if possible since it will involves 2 millions rows and can takes forever) can I use to returns this result? The business rule is to collapses the first 6 rows since they have consecutive numbers.
ColA ColB ColC
00100 295.00 295.05
00100 295.10 295.15
00100 295.98 295.98
Sample table codes
CREATE TABLE TEST
(ColA varchar(6),
ColB money,
ColC money)
INSERT TEST (ColA, ColB, ColC)
select '00100','295.00','295.00'
UNION
select '00100','295.01','295.01'
UNION
select '00100','295.02','295.02'
UNION
select '00100','295.03','295.03'
UNION
select '00100','295.04','295.04'
UNION
select '00100','295.05','295.05'
UNION
select '00100','295.10','295.15'
Thanks
November 19, 2008 at 3:48 pm
I have hope that you could use the over() clause, maybe with a WITH() subquery joined in ?
might need a CASE thrown in for good measure.
http://msdn.microsoft.com/en-us/library/ms189461.aspx
you'll need to be far more clever than I am to accomplish this 🙂
~BOT
Craig Outcalt
November 19, 2008 at 8:21 pm
I was trying to see if I could do this without using the running count trick, and here's the ugly thing I came up with:
with cte1 as (
select row_number() over (order by t1.cola, t1.colb) rn,
t1.*,
case when t2.cola is null then 1 else 0 end start
from Test T1
left outer join Test t2 on t1.cola=t2.cola and t1.colb=t2.colc+.01
),
cte2 as (
select rn,
row_number() over (order by RN) RowN
from CTE1
where start=1
),
cte3 as (
select row_number() over (order by c2a.rn) r,
c2a.rn st,
c2b.rn-1 ending
from cte2 c2a
left join cte2 c2b on c2a.rowN=c2b.rowN-1
)
select cte3.r,
cte1.cola,
min(cte1.colb)low,
max(cte1.colc) high
from cte1
join cte3 on cte1.rn between cte3.st and isnull(cte3.ending,cte3.st)
group by cte3.r,cte1.cola
Without any indexing the performance will SUCK. At very least you may need two indexes (one on cola+colb, one on cola+colc), but even that will suck unless you index a computed column.
That being said - on a small set - it does in fact work.
I'd still look at implementing a running aggregate of sorts- it should do better for you in the end:
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/[/url]
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply