Geek Answers Handbook

Difficult SQL query with sequential values

I need to perform a relatively simple explanation, but (given my somewhat limited skills) it is difficult to write an SQL query.

Suppose we have a table like this:

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 not necessarily related, as you might expect from the type I selected.

Now the query I need to do is as follows:

  • In the final exam ( exam_no = 3), find all the students who failed ( John Doe , Ryan Smith and Mary Lee ).
  • For each of these students, find the date of the first in a series of successively failed exams. Another way would be: for each of these students to find the date of the first failed exam that comes after the last exam. (Look at the arrows in the table).

As a result, the table should look 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 fulfill such a request?

Thank you for your time.

+8
sql database mysql
source share
4 answers

You can take advantage of the fact that if someone passed the last exam, then they have not had any exams since their last pass: therefore, the problem boils down to the fact that the first exam has not passed since the last 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

Take a look at sqlfiddle .

+4
source share

I have to use the subquery that the last exam passed, somthing like:

 SET @query_exam_no = 3; SELECT name, surname, MIN(IF(date > last_passed_exam, date, NULL)) AS date_failing_since FROM exam_results LEFT JOIN ( SELECT name, surname, MAX(date) AS last_passed_exam FROM exam_results WHERE result = 'PASS' GROUP BY name, surname ) AS last_passed_exams USING (name, surname) HAVING MAX(IF(exam_no = @query_exam_no, result, NULL)) = 'FAIL' GROUP BY name, surname
0
source share

It's enough:

 select t.name, t.surname, t.date as 'date_since_failing' from tablename t inner join ( select name, surname, max(exam_no) as exam_no from tablename group by name, surname having min(result) = 'FAIL' ) aux on t.name = aux.name and t.surname = aux.surname and t.exam_no = aux.exam_no
0
source share

The condition you are asking for is not suitable for anything, without it. Here is a working example.

 select e.name, e.sur_name, min(e.date) as `LastFailed` from exams as e where e.result = 'Fail' group by e.name order by e.name

It gives the result

 name sur_name LastFailed Ann Evans 2012-02-03 John Doe 2012-02-01 Mary Lee 2012-01-04 Ryan Smith 2012-01-02
0
source share

More articles:


All Articles