SQL to pull consecutive numbers rows

5 pts.
Tags:
SQL
SQL Query
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
ASKED: November 19, 2008  10:49 PM
UPDATED: November 20, 2008  3:08 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following