How to find duplicate consecutive values ​​in this table?

Question

Let's say I have a table that I query like this:

select date, value from mytable order by date 

and this gives me the results:

 date value 02/26/2009 14:03:39 1 02/26/2009 14:10:52 2 (a) 02/26/2009 14:27:49 2 (b) 02/26/2009 14:34:33 3 02/26/2009 14:48:29 2 (c) 02/26/2009 14:55:17 3 02/26/2009 14:59:28 4 

I am interested in the rows of this result set, where the value matches the value in the previous or next row, for example, row b, which has value = 2, is the same as row a. I don't need rows like string c, which has value = 2, but doesn't come right after row with value = 2. How can I query a table to give me all rows like a and b? This applies to Oracle, if that matters.

Answer 1

Use analytic functions for lead and lag.

 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 ; 

Corresponding neighbors are marked * in the match column,

alt text http://i28.tinypic.com/2drrojt.png

Answer 2

This is a simplified version of @Bob Jarvis's answer, the main difference is using only one subquery instead of four,

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

Answer 3

As @Janek Bogucki, LEAD, and LAG explained, probably the easiest way to do this - but just for fun, try to do this using only basic connection operations:

 SELECT mydate, VALUE FROM (SELECT a.mydate, a.value, CASE WHEN a.value = b.value THEN '*' ELSE NULL END AS flag1, CASE WHEN a.value = c.value THEN '*' ELSE NULL END AS flag2 FROM (SELECT ROWNUM AS outer_rownum, mydate, VALUE FROM mytable ORDER BY mydate) a LEFT OUTER JOIN (select ROWNUM-1 AS inner_rownum, mydate, VALUE from mytable order by myDATE) b ON b.inner_rownum = a.outer_rownum LEFT OUTER JOIN (select ROWNUM+1 AS inner_rownum, mydate, VALUE from mytable order by myDATE) c ON c.inner_rownum = a.outer_rownum ORDER BY a.mydate) WHERE flag1 = '*' OR flag2 = '*'; 

Share and enjoy.