SQL to pull consecutive numbers rows

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 2005 Forums

Transact-SQL (2005)

SQL to pull consecutive numbers rows

Author

Topic

nhuanlieu
Starting Member

27 Posts

Posted - 2008-11-19 : 16:27:06

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 is below if you want to try it out.

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

nr
SQLTeam MVY

12543 Posts

Posted - 2008-11-19 : 20:47:46

Are Col1B and ColC start and end?
so could be
00100 295.00 295.02
00100 295.03 295.03

and you would want
00100 295.00 295.03

This isn't the quickest way but you only have a couple of million rows and might do - at least it will check the requirements.
to get the max of ranges
select *
from tbl t
where not exists (select * from tbl t2 where t.ColA = t2.ColA and t2.ColB = t.ColC + 0.01)

to get the min of ranges
select *
from tbl t
where not exists (select * from tbl t2 where t.ColA = t2.ColA and t2.ColB = t.ColC - 0.01)

then it's just a matter of putting them together
I add a sequence to the min and max for the join. There must always be a min and max so should be safe to order them like this.

select tmin.ColA, tmin.ColB, tmax.ColC
from
(
select *, seq = rank() over (partition by ColA order by ColB)
from tbl t
where not exists (select * from tbl t2 where t.ColA = t2.ColA and t2.ColB = t.ColC + 0.01)
) tmax
join
(
select *, seq = rank() over (partition by ColA order by ColB)
from tbl t
where not exists (select * from tbl t2 where t.ColA = t2.ColA and t2.ColB = t.ColC - 0.01)
) tmin
on tmax.ColA = tmin.ColA
and tmax.seq = tmin.seq

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 01:33:58

see this too
http://www.sqlteam.com/article/detecting-runs-or-streaks-in-your-data

nr
SQLTeam MVY

12543 Posts

Posted - 2008-11-20 : 16:21:33

Possibly using the streak technique might work but depends on the requirements.
When you're looking for consecutive ranges it tends not to be so efficient and more complicated.
It's why I posted the above first to see what you really want.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-21 : 02:31:54

[code]DECLARE @Sample TABLE
(
colA VARCHAR(6),
colB MONEY,
colC MONEY,
seq INT,
UNIQUE CLUSTERED
(
colC
)
)

INSERT @Sample
(
colA,
colB,
colC
)
SELECT '00100', 295.00, 295.00 UNION ALL
SELECT '00100', 295.01, 295.01 UNION ALL
SELECT '00100', 295.02, 295.02 UNION ALL
SELECT '00100', 295.03, 295.03 UNION ALL
SELECT '00100', 295.04, 295.04 UNION ALL
SELECT '00100', 295.05, 295.05 UNION ALL
SELECT '00100', 295.10, 295.15 UNION ALL
SELECT '00100', 295.98, 295.98

DECLARE @colA VARCHAR(6),
@colB MONEY,
@colC MONEY,
@seq INT

UPDATE @Sample
SET @seq = seq = CASE WHEN colA = @colA AND colB = @colB + 0.01 AND colC = @colC + 0.01 THEN @seq ELSE COALESCE(@seq, 0) + 1 END,
@colA = colA,
@colB = colB,
@colC = colC

SELECT MIN(colA) AS ColA,
MIN(colB) AS colB,
MAX(colC) AS colC
FROM @Sample
GROUP BY seq
ORDER BY seq[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources