BFWebAdmin BFWebAdmin - 7 months ago 11
SQL Question

Check if a query returns 4 sequential identical values in MySQL

I want to check if there have been four identical values in a row (by which I mean one after the other, rather than in a table row) in MySQL. So, if I'm checking on the value "attachment", the following query result would be false:

type_count post_type
1 post
2 attachment
3 post
4 post
5 attachment
6 post
7 attachment
8 post
9 post
10 post
11 post
12 attachment
13 post
14 post
15 post


Whereas this dataset would prove true:

type_count post_type
1 post
2 attachment
3 post
4 post
5 attachment
6 attachment
7 attachment
8 attachment
9 post
10 post
11 post
12 attachment
13 post
14 post
15 post


Is this possible? Working from a query result rather than a table.

Answer

If the query below:

SELECT COUNT(*)
FROM (
  SELECT type_count, post_type,
         @seq := IF(@pt = post_type, @seq+1,
                    IF(@pt := post_type, 1, 1)) AS seq
  FROM mytable
  CROSS JOIN (SELECT @seq := 0, @pt := '') AS vars) AS t
WHERE post_type = 'attachment' AND seq >= 4 

returns a number that is equal to or greater than 1, then your criteria is true, otherwise it is false.

Comments