CODEWITHSUNDEEP
sqlpostgresql
Rp23
Rp23

Reputation: 23

How can I find the values that repeat themselves consecutively at least 3 times using PostgreSQL

I am looking for the values that repeat themselves at least three times on a sequentially sorted table by an increasing ID.

For example:

Sequential Code
01 2301
02 1909
03 3310
04 3310
05 3310
06 2300
07 1999

In this case, I would need the output to be

Code
3310

As this is the only code that repeats itself three times consecutively (note that the code might repeat itself many times in the table, but if it's not consecutive, it doesn't meet the condition.

How can I do this using PostgreSQL?

Upvotes: 2

Views: 532

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

Use lead(). To get the first value of a sequence:

select t.*
from (select t.*,
             lead(sequential, 2) over (order by sequential) as sequential_2,
             lead(sequential, 2) over (partition by code order by sequential) as sequential_code_2
      from t
     ) t
where sequential_code_2 = sequential_2;

If you really just want the codes, use select distinct code.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

This is really a gaps and islands problem. You are asking which island of code records occurs three at a time. We can use the difference in row numbers method here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY Sequential) rn1,
              ROW_NUMBER() OVER (PARTITION BY Code ORDER BY Sequential) rn2
    FROM yourTable
)

SELECT DISTINCT Code
FROM cte
GROUP BY (rn1 - rn2), Code
HAVING COUNT(*) = 3;

Demo

Note that the reason for using DISTINCT above is that, possibly, a given code could appear in an island of 3 more than once. But, your requirement above is that you only want to report a matching code once.

Upvotes: 2

Related Questions