Selecting consecutive values

  • Can someone help me with a solution for this.

    Say I have a table with columns a, b, c, d. now lets say column c holds consecutive values like 1,2,3 ...

    How can I select the row that follow consecutively, and if there is an enterval in the value like value jumping from 6 to 9, I need to select from 9,10... to the nwxt row?

    Someone please.

     

     

  • I'm not dure if I understand 100% my first answer is order asc by colomn c. If that is not what you meant and you want to loop through the records you could use a cursor or a while and still order by colomn c. if you are having a problem with selecting the nex row based on the numbers like 6..9 then declare a vairable and after eac select set it equal to the value in colomn c then in your select you will select the next min value in colomn c that is greater than the vairable.

    Hope this helps.

  • This may help get you going in the right direction.  I recently learned this handy trick thanks to this site.

    -- Create a source table with IDs missing

    -- to mimic user's situation

    declare @Source table (SourceID int)

    insert

    into @Source values (1)

    insert into @Source values (2)

    insert into @Source values (3)

    insert into @Source values (7)

    insert into @Source values (10)

    insert into @Source values (11)

    insert into @Source values (12)

    insert into @Source values (15)

    insert into @Source values (19)

    insert into @Source values (25)

    -- Create a "comparison" table that

    -- contains contiguous numbers

    declare @Comparison table (ComparisonID int)

    -- Only seed the Comparison table

    -- based on the highest ID in our

    -- source table

    declare @MaxCounter int

    select @MaxCounter = max(SourceID)

    from @Source

    declare @Counter int

    set @Counter = 1

    while @Counter <= @MaxCounter

         begin

              insert into @Comparison values (@Counter)

              set @Counter = @Counter + 1

         end

    -- Now do a left join to figure out what

    -- the missing IDs are

    select c.ComparisonID

    from @Comparison c

    left join @Source s on c.ComparisonID = s.SourceID

    where s.SourceID is null

    Hope this helps!

  • Maybe this

    SELECT a.c As [ThisRow],

    (SELECT MIN(b.c) FROM b WHERE b.c > a.c) As [NextRow]

    FROM a

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Thanks LSAdvantage, that really went a long way.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply