none
SQL to pull consecutive numbers rows

    Question

  • 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

    Wednesday, November 19, 2008 9:30 PM

All replies

  • Hi

     

    Try this:

    declare @IncrStep money

    set       @IncrStep = 0.01

    select * from TEST t1

    where not exists  -- find all the records where previous step does not exist

     (select * from Test t2

    where t1.Colb = t2.Colb + @IncrStep

              )

     

    Wednesday, November 19, 2008 11:07 PM
  • ARe you using SQL2005 or later?

     

    Thursday, November 20, 2008 12:58 AM
  • Go it:

     

    Code Snippet

    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'

    union

    select '00100','295.98','295.98'

     

    select *

    from TEst

    where not exists(

    select 1 from Test as [T2]

    where Test.Cola = T2.ColA

    and Test.ColB - 0.01 = T2.ColB

    )

     

     

     

     

    Thursday, November 20, 2008 1:06 AM
  • yes, we use SQL 2005.
    Thursday, November 20, 2008 4:24 PM
  •  

    Hi.

    The same idea rusag2 used in his answer, just different implementation:

     

    select t.*

    from MyTable t

    left outer join MyTable tt

    on t.cola=tt.cola

    and t.colb - 0.01 = tt.colb

    where tt.cola is null

    Thursday, November 20, 2008 6:55 PM