How to check for the same column value on next row

Question

I have a table that gives me data regarding the journey of a proposal. A proposal may hit different statuses, which are then mapped to a milestone. A status may be hit more than once in succession, but we don't want the milestone to repeat.

ID	MilestoneID
1	1
1	2
1	3
1	3

So in the example above, this would be a problem. How can I write a query that tells me if a milestone ID is the same on the next row in the journey table?

SELECT ID, MilestoneID, count(*) as vol
FROM Journey
GROUP BY ID, MilestoneID
HAVING count(*) > 1

The above code tells me that we have instances where the Milestone is repeated, but I need to know if they are on successive rows.

Answer 1

ROW_NUMBER() can be of use.

with milestone as ( 
    select 1 Id, 1 MilestoneId 
    union all 
    select 1 Id, 2 MilestoneId 
    union all 
    select 1 Id, 3 MilestoneId 
    union all 
    select 1 Id, 3 MilestoneId 
) 
SELECT *, 
       CASE WHEN ROW_NUMBER() OVER (PARTITION BY MilestoneId ORDER BY MilestoneId ASC) > 1 THEN 'Same Milestone Id' 
            ELSE NULL 
       END Status 
FROM milestone

Check Same Milestone Ids

Answer 2

You can do it as follows using lead() function :

First, we need to create an id for each record; if you already have a unique id, you can skip this CTE.

Cte2 will check to see if two successif rows are equal, and if they are, we will use the having clause having sum(next_value) > 0 to get it.

with cte as (
  select ID, MilestoneID, row_number() over (order by (select null)) as rn
  from Journey
),
cte2 as (
  select *,
     lead(MilestoneID) over (partition by ID order by rn) as lead_val,
     CASE WHEN MilestoneID = lead(MilestoneID) over (partition by ID order by rn) THEN 1 ELSE 0 END as next_value
  from cte
)
select ID, MilestoneID
from cte2
group by ID, MilestoneID
having sum(next_value) > 0

Result :

ID  MilestoneID
1   3

Demo here

Answer 3

Use ROW_NUMBER(),

Select ID, MilestoneID 
From ( 
      Select ID, MilestoneID, RN=Row_Number()  over(PARTITION BY ID, MilestoneID ORDER BY MilestoneID ) 
      FROM Journey )X
Where X.RN=2