How to select where there are 2 consecutives rows with a specific value using MySQL?

Question

I'm building a system that should show when the students missed two days in a row. For example, this table contains the absences.

day         |   id  |   missed
----------------------------------
2016-10-6   |   1   |   true
2016-10-6   |   2   |   true
2016-10-6   |   3   |   false

2016-10-7   |   1   |   true
2016-10-7   |   2   |   false
2016-10-7   |   3   |   true

2016-10-10  |   1   |   false
2016-10-10  |   2   |   true
2016-10-10  |   3   |   true

(days 2016-10-8 and 2016-10-9 are weekend)

in the case above:

student 1 missed the days 1st and 2nd. (consecutive)
student 2 missed the days 1st and 3rd. (nonconsecutive)
student 3 missed the days 2nd and 3rd. (consecutive)

The query should select only student 1 and 3.

Is possible to do stuff like this just with a single SQL Query?

Nir Levy · Accepted Answer · 2016-02-15T22:28:00.870

Use inner join to connect two instances of the table- one with the 'first' day, and one with the 'second' day, and then just look for rows where both are missed:

select a.id from yourTable as a inner join yourTable as b 
  on a.id = b.id and a.day = b.day-1 
  where a.missed = true and b.missed = true

EDIT

Now that you changed the rules... and made it date and not int in the day column, this is what I'll do:

Use DAYOFWEEK() function to go to a day as a number
Filter out weekends

use modulo to get Sunday as the next day of Thursday:

select a.id from yourTable as a inner join yourTable as b 
  on a.id = b.id and DAYOFWEEK(a.day) % 5 = DAYOFWEEK(b.day-1) % 5 
  where a.missed = true and b.missed = true
  and DAYOFWEEK(a.day) < 6 and DAYOFWEEK(b.day) < 6

it is a little more complicated than it. I just edited the question. — Daniel Santos, Feb 15 '16 at 22:20

score 2 · Answer 2 · answered Feb 15 '16 at 21:49

2

similar approach as other answers, but different syntax

select distinct id
from t
where
    missed=true and
    exists (
        select day
        from t as t2
        where t.id=t2.id and t.day+1=t2.day and t2.missed=true
    )

answered Feb 15 '16 at 21:49

Iłya Bursov

23,342
4
33
57

it is a little more complicated than it. I just edited the question. – Daniel Santos Feb 15 '16 at 22:18
1

@DanielSantos then you need to add OR condition like `( (t.day+1=t2.day) or (DAYOFWEEK(t.day) == 6 AND t.day+3=t2.day) ) – Iłya Bursov Feb 15 '16 at 22:23

score 0 · Answer 3 · answered Feb 15 '16 at 21:45

0

This will give you each instance of it happening. You'll get multiple hits if they have 3 or more consecutive days missed, so if that's an issue then you'll need to refine it.

SELECT
    T1.id,
    T1.day
FROM
    My_Table T1
INNER JOIN My_Table T2 ON
    T2.id = T1.id AND
    T2.day = T1.day + 1 AND
    T2.missed = true
WHERE
    T1.missed = true

answered Feb 15 '16 at 21:45

Tom H

46,766
14
87
128

it is a little more complicated than it. I just edited the question. – Daniel Santos Feb 15 '16 at 22:18

score 0 · Answer 4 · answered Feb 15 '16 at 21:51

You can do it using variables:

SELECT DISTINCT id
FROM (
  SELECT day, id, missed,
         @rn := IF(@id = id, 
                   IF(missed = true, @rn + 1, 0),
                   IF(@id := id, 
                      IF(missed = true, 1, 0), 
                      IF(missed = true, 1, 0))) AS rn                          
  FROM mytable
  CROSS JOIN (SELECT @rn := 0, @id := 0) AS var
  ORDER BY id, day) AS t
WHERE t.rn >= 2

The benefit of the above method is that it is scalable: it can be easily extended to check for more than 2 consecutive records of student absences.

Demo here

it is a little more complicated than it. I just edited the question. — Daniel Santos, Feb 15 '16 at 22:18

score 0 · Answer 5 · answered Feb 15 '16 at 23:20

0

Here's one way...

SELECT x.id
  FROM my_table x
  JOIN my_table y
    ON y.id = x.id 
   AND y.missed = x.missed
   AND y.day > x.day
   AND 5 * (DATEDIFF(y.day, x.day) DIV 7) + MID('0123444401233334012222340111123400001234000123440', 7 * WEEKDAY(x.day) + WEEKDAY(y.day) + 1, 1) <= 1
 WHERE x.missed = 1;

answered Feb 15 '16 at 23:20

Strawberry

33,750
13
40
57

Can you explain the last AND ? – Daniel Santos Feb 16 '16 at 00:07
1

@DanielSantos http://stackoverflow.com/questions/1828948/mysql-function-to-find-the-number-of-working-days-between-two-dates – Strawberry Feb 16 '16 at 00:21

How to select where there are 2 consecutives rows with a specific value using MySQL?

5 Answers5

Linked