Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Need to get consecutive duplicate rows RE: Need to get consecutive duplicate rows

  • February 17, 2018 at 4:24 pm

    #1980187

    Jeff Moden - Saturday, February 17, 2018 4:11 PM

    Jacob Wilkins - Saturday, February 17, 2018 2:33 PM

    Jeff,

    Well aware of that. It was not specified that an id of, say, 7 counts as consecutive with an id of 5 so long as there are no rows with an id of 6. If that's the case,  then yes, a change on those lines is needed. 

    Then again, maybe the requirement is that 5 and 7 are not to be counted as consecutive in the scenario above.

    The queries were provided to meet the specifications provided. If the specifications are different, then yes, the queries will have to be different 🙂

    Understood but where did the OP state that the sequence numbers in ColA were guaranteed to not contain any gaps in the numeric sequence?  My suggestion is to always assume the worst when such things aren't specified.

    ColA would be Identity, but there could still be gaps (rollback on error, etc).

    Let me explain the original problem:
    Users could click many times, not realizing they were updating the item over and over (bad latency protection, I know). Each one of those updates caused a history snapshot in a history table (simulated in the table in my original post). Since the users are paid by the click, you can see the problem. I need to remove repetitive rows, indicated by exact ColC-D-E rows, in sequence one after another, with no changes in between, grouped by ColB. Column A is the IDENTITY pk.

    I believe the solution put forth by Jacob Wilkins satisfies the goal.