MySQL: How to find sequence of values in column

Question

I have long list of rows with random values:

| id | value |
|----|-------|
|  1 | abcd  |
|  2 | qwer  |
|  3 | jklm  |
|  4 | yxcv  |
|  5 | tzui  |

Then I have an array of few values:

array('qwer', 'jklm');

And I need to know, if this sequence of values from array already exists in table in given order. In this case the sequence of values exists.

I tried to concat all values from table and array and match two strings, which works great with few rows but there are actually hundred of thousand of rows in table. I believe there should be a better solution.

Actually I have about 50 values, but I can pick up e.g. first 20 to be sure that the sequence is not exists yet - I need to know if sequence is unique. — hovado
– hovado, Commented Jan 6, 2018 at 0:26

Bill Karwin · Accepted Answer · 2018-01-05 17:46:25Z

2

If your list is short, you could just do a self-join and spell out the conditions for each joined table reference:

select t1.id from MyTable as t1 join MyTable as t2 
where t1.value='qwer' and t2.value='jklm' and t1.id=t2.id-1;

This returns an empty set if there's no such sequence. And of course it assumes that the id numbers are consecutive (they are in your example, but in general that's a risky assumption).

This doesn't work well if your list gets really long. There's a hard limit of 63 table references MySQL supports in a single query.

Here's another solution, which works for any size list, but only if your id values are known to be consecutive:

select t1.id from MyTable as t1 join MyTable as t2 
  on t2.id between t1.id and t1.id+1
where t1.value = 'qwer' and t2.value in ('qwer','jklm')
group by t1.id
having group_concat(t2.value order by t2.id) = 'qwer,jklm';

The t1 row is the beginning of the potential matching sequence of rows, so it must match the first value in your list.

Then join to the t2 rows, which are the complete set of potentially matching rows.

The set of t2 rows is also limited to a set no more than N rows, based on the size of your list of N values you're searching for. But SQL has no way of making a group based on the number of rows, we can only limit based on some value in the row. So that's why this works if your id values can be assumed to be consecutive.

edited Jan 5, 2018 at 17:46

answered Jan 5, 2018 at 17:34

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Strawberry Over a year ago

I wonder if there are any other situations in which this would not work.

Bill Karwin Over a year ago

@Strawberry, I understood your comment only because I know you are one of the snarkiest users on Stack Overflow (that is not a compliment).

Strawberry Over a year ago

I was counting on that ;-)

hovado Over a year ago

I think I can select only first 10 to 20 values from the list in my case to safely match whole list. Your second query works great and its fast as well. Thanks again, Bill :)

Dejan Dozet · Accepted Answer · 2018-01-06 09:20:22Z

1

This way you can do it for the whole set:

select value1, value2
from
(
    select *
    from (
        SELECT [IMEPAC] value1 , ROW_NUMBER() over(order by [MATBR]) rn1
          FROM [PACM]
    ) a1 join
        (
        SELECT [IMEPAC] value2 , ROW_NUMBER() over(order by [MATBR]) rn2
          FROM [PACM]
    ) a2 on a1.rn1 = a2.rn2 + 1
) a
group by value1, value2
having count(*) > 1

It is written for MS SQL but you can easily rewrite it to fit mysql too.

I run this against table with > 400000 rows on IMEPAC which is not part of any index and it run (first and only once) for 6 sec.

Here is Mysql version:

select value1, value2, count(*) count
from
(
    select *
    from (
      SELECT @row_number1:= @row_number1 + 1 AS rn1, content as value1
      FROM docs,(SELECT @row_number1:=0) AS t
      order by id
    ) a1 join
    (
      SELECT @row_number2:= @row_number2 + 1 AS rn2, content value2
      FROM docs,(SELECT @row_number2:=0) AS t
      order by id
    ) a2 on a1.rn1 = a2.rn2 + 1
) a
group by value1, value2
having count(*) > 1;

SQL Fiddle here

edited Jan 6, 2018 at 9:20

answered Jan 5, 2018 at 20:50

Dejan Dozet

1,07712 silver badges35 bronze badges

2 Comments

hovado Over a year ago

Thanks Dejan for your answer but I can't use this because OVER() clause is in MySQL 8 and hosting currently support version 5.6.

Dejan Dozet Over a year ago

You are welcome! I have been researching and found out how you can make row_number in mysql, try it yourself - I added mysql server here too

Collectives™ on Stack Overflow

MySQL: How to find sequence of values in column

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related