How can I find duplicate consecutive values in this table?

Use the lead and lag analytic functions.

create table t3 (d number, v number);
insert into t3(d, v) values(1, 1);
insert into t3(d, v) values(2, 2);
insert into t3(d, v) values(3, 2);
insert into t3(d, v) values(4, 3);
insert into t3(d, v) values(5, 2);
insert into t3(d, v) values(6, 3);
insert into t3(d, v) values(7, 4);

select d, v, case when v in (prev, next) then '*' end match, prev, next from (
  select
    d,
    v,
    lag(v, 1) over (order by d) prev,
    lead(v, 1) over (order by d) next
  from
    t3
)
order by
  d
;

Matching neighbours are marked with * in the match column,

alt text


This is a simplified version of @Bob Jarvis' answer, the main difference being the use of just one subquery instead of four,

with f as (select row_number() over(order by d) rn, d, v from t3)
select
  a.d, a.v,
  case when a.v in (prev.v, next.v) then '*' end match
from
  f a
    left join
  f prev
    on a.rn = prev.rn + 1
    left join
  f next
    on a.rn = next.rn - 1
order by a.d
;

Tags:

Sql

Oracle