2

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!

The Impaler
  • 45,731
  • 9
  • 39
  • 76
blogo
  • 319
  • 1
  • 12
  • what does `select version();` show? – ysth Oct 09 '21 at 00:45
  • so you are specifically looking for the pattern of val 29, 21, 14, and no other? what makes rows consecutive, ids being in order with no interposing rows, or something else? – ysth Oct 09 '21 at 00:46
  • Consecutive as in no interposing rows. I am using HeidiSQL 9.5, select version() shows 10.1.37-MariaDB – blogo Oct 09 '21 at 00:58
  • I am not specifically looking for that pattern, it will be whatever the user inputs, but for the purpose of figuring out the query syntax we can use that pattern. I will drop the user input into vars and use the vars in the query. – blogo Oct 09 '21 at 01:01
  • 1
    the easiest way to do this is with the lag() or lead() function, but that requires mariadb 10.2 or mysql 8 – ysth Oct 09 '21 at 01:21

1 Answers1

4

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)
Ajax1234
  • 69,937
  • 8
  • 61
  • 102
  • Ajax1234, you are way over my head, but many thanks. I will look into lag and lead. – blogo Oct 09 '21 at 01:04
  • 1
    @blogo Glad to help. You might find [this](https://riptutorial.com/sql/example/27455/lag-and-lead) link on `lead` and `lag` helpful. – Ajax1234 Oct 09 '21 at 01:07