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,
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
;