I have a very simple table, similar to:
id | val
----------
1 | 20
2 | 22
3 | 29
4 | 21
5 | 14
6 | 9
7 | 15
I want to be able to identify a pattern in consecutive rows. For example, if the input is "29, 21, 14", there are three consecutive rows that match and I want to return:
id | val
----------
3 | 29
4 | 21
5 | 14
If the rows are not consecutive there is no match.
My attempt, which does not work:
SELECT id as firstid, val from tbl100 WHERE `val` = '29' AND firstid+1 = '21' AND firstid+2 = '14'
The problem is obviously that my query finds val 29, then looks for id 21 and id 14, it should look for val 21 and val 14, but I have no idea how to do that.
Any help appreciated!
You can first find the matching rows and then check for sequential ids with lead and lag:
with m_vals as (
select t1.* from t t1 join i t2 on t1.val = t2.val
)
select t3.id, t3.val from (select m.*, lag(m.id) over (order by m.id) l, lead(m.id) over (order by m.id) t from m_vals m) t3
where (t3.l is null or t3.l + 1 = t3.id) and (t3.t is null or t3.t - 1 = t3.id)