Question 114216 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – sql – find duplicates on second order

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
Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search