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 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
Software/Hardware used:
Software/Hardware used:
ASKED:
November 19, 2008 10:49 PM
UPDATED:
November 20, 2008 3:08 PM
Answer Wiki:
Based on your example, I think this query could do the trick, but the subquery will perform a full table scan (as the main query), unless an index exists on (colB,colC).
<pre>select * from test t
where not exists
(select 1 from test where colB = t.colB + 0.01 and colC = t.colC + 0.01);</pre>
You did not specify what database are you using, so the syntax may need some changes.
The same result could be accomplished using an outer join (this is oracle syntax, so please make the necessary changes):
<pre>select * from test t, test t2
where t.colB+0.01 = t2.colB(+)
and t.colC+0.01 = t2.colC(+)
and t2.colB is null</pre>
Hope this helps.
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
