Tricky SQL query involving consecutive values

Question

I need to perform a relatively easy to explain but (given my somewhat limited skills) hard to write SQL query.

Assume we have a table similar to this one:

 exam_no | name | surname | result | date
---------+------+---------+--------+------------
 1       | John | Doe     | PASS   | 2012-01-01
 1       | Ryan | Smith   | FAIL   | 2012-01-02 <--
 1       | Ann  | Evans   | PASS   | 2012-01-03
 1       | Mary | Lee     | FAIL   | 2012-01-04
 ...     | ...  | ...     | ...    | ...
 2       | John | Doe     | FAIL   | 2012-02-01 <--
 2       | Ryan | Smith   | FAIL   | 2012-02-02
 2       | Ann  | Evans   | FAIL   | 2012-02-03
 2       | Mary | Lee     | PASS   | 2012-02-04
 ...     | ...  | ...     | ...    | ...
 3       | John | Doe     | FAIL   | 2012-03-01
 3       | Ryan | Smith   | FAIL   | 2012-03-02
 3       | Ann  | Evans   | PASS   | 2012-03-03
 3       | Mary | Lee     | FAIL   | 2012-03-04 <--

Note that exam_no and date aren't necessarily related as one might expect from the kind of example I chose.

Now, the query that I need to do is as follows:

• From the latest exam (exam_no = 3) find all the students that have failed (John Doe, Ryan Smith and Mary Lee).
• For each of these students find the date of the first of the batch of consecutively failing exams. Another way to put it would be: for each of these students find the date of the first failing exam that comes after their last passing exam. (Look at the arrows in the table).

The resulting table should be something like this:

 name | surname | date_since_failing
------+---------+--------------------
 John | Doe     | 2012-02-01
 Ryan | Smith   | 2012-01-02
 Mary | Lee     | 2012-03-04

How can I perform such a query?

Thank you for your time.

Answer 1

You can take advantage of the fact that if someone passed the most recent exam, then they have not failed any exams since their most recent pass: therefore the problem reduces to finding the first exam failed since the most recent pass:

SELECT   name, surname, MIN(date) date_since_fail
FROM     results NATURAL LEFT JOIN (
  SELECT   name, surname, MAX(date) lastpass
  FROM     results
  WHERE    result = 'PASS'
  GROUP BY name, surname
) t
WHERE    result = 'FAIL' AND date > IFNULL(lastpass,0)
GROUP BY name, surname

See it on sqlfiddle.