Can pattern matching be used in MySQL for consecutive rows?

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!

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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)


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x