Sqlite - Return rows where some values are different-CodePudding

I have a large table a SQLite database of student exam results. Students often resit failed exams and subsequently pass them as shown with student 10001 below.

 ───────────── ──────── ────────────── ────────── ──────── ───────────── 
| student_id  | level  | name         | outcome  | grade  | date        |
 ───────────── ──────── ────────────── ────────── ──────── ───────────── 
| 10001       | Higher | Mathematics  | Pass     | A      | 01/04/2022  |
| 10001       | Higher | Mathematics  | Fail     | F      | 01/02/2022  |
| 10002       | Higher | English      | Pass     | B      | 01/04/2022  |
 ───────────── ──────── ────────────── ────────── ──────── ─────────────

I would like to be able to do two things with this data.

Return only the failed exam should it have a also have a pass entry recorded. In other word the row 2nd from the top in the image table.
Return the whole table but this time without the initial failed exam. In other words everything minus the results from 1.

The exam is considered a resit if the student_id, level and name all match. The other values in the rows can be different.

Any help appreciated.

CodePudding user response：

For the 1st resultset that you want, you can use EXISTS in the WHERE clause:

SELECT t1.*
FROM tablename t1
WHERE t1.outcome = 'Fail'
  AND EXISTS (
        SELECT 1
        FROM tablename t2
        WHERE (t2.student_id, t2.level, t2.name) = (t1.student_id, t1.level, t1.name)
          AND t2.date > t1.date 
          AND t2.outcome = 'Pass'
      );

and for the 2nd resultset, since the requirement is:

everything minus the results from 1

the simplest way to get it is with the 1st query and EXCEPT:

SELECT * 
FROM tablename
EXCEPT
SELECT t1.*
FROM tablename t1
WHERE t1.outcome = 'Fail'
  AND EXISTS (
        SELECT 1
        FROM tablename t2
        WHERE (t2.student_id, t2.level, t2.name) = (t1.student_id, t1.level, t1.name)
          AND t2.date > t1.date 
          AND t2.outcome = 'Pass'
      );

See the demo.

Note that, if the format of the dates in the table is the same as the sample data in your question then these dates are not comparable.
You should change the format to YYYY-MM-DD.