sql - find duplicates on second order-CodePudding

I have a color table t1 with data like below, sorted by sn

select * from t1 order by sn
 --- ------- 
|sn |color  |
 --- ------- 
|25 |Brown  |
|29 |Brown  |
|33 |Blue   |
|37 |Red    |
|42 |Green  |
|46 |Yellow |
|49 |Red    |
|53 |Red    |
|57 |Red    |
|61 |Blue   |
|65 |Red    |
|69 |Pink   |
 --- -------

I need to find 2 or more adjacent rows having same color when sorted by sn.

|25 |Brown  |
|29 |Brown  |

|49 |Red    |
|53 |Red    |
|57 |Red    |

I tried something like the lag() function in SQL, but that gets only 2 adjacent rows. I'm not able to expand the solution to address it in a generic way. Can this be solved.

select sn, color, lag(color) over(order by sn) prev_color from t1

CodePudding user response：

A row should be returned if its color is the same as the previous or the next row:

SELECT sn, color
FROM (  
  SELECT *,
         LAG(color) OVER (ORDER BY sn) prev_color,
         LEAD(color) OVER (ORDER BY sn) next_color
  FROM tablename
) t
WHERE color IN (prev_color, next_color);

See the demo.

CodePudding user response：

You could use a simple join:

select t.sn, t.color from t1 t
left join (
  select
     sn,
     lag(color) over(order by sn) as lag,
     lead(color) over(order by sn) as lead
  from t1 
) l on t=sn = l.sn
where t.color = l.lag or t.color=l.lead