sql - SQL: How to select pass rows from student table in case of re-sit exam in postgresql?

Question

Say I have a table which has the data of students and their results.

ID  Result
1   PASS
2   PASS
3   PASS
3   FAIL
4   PASS
4   FAIL
4   FAIL
5   FAIL
5   FAIL
5   FAIL

And I want to select one row for each student if they pass, I want pass rows to return but if they don't then return one fail row.

This is the expected result:

ID  Result
1   PASS
2   PASS
3   PASS
4   PASS
5   FAIL

Note: Students can re-sit the exam as many times as they wish until they get a pass or give up from the exam.

Thank you.

标签： sqlpostgresql

Answer 1

Do a GROUP BY. Since PASS (always) is greater than FAIL, use MAX().

select ID, max(Result)
from tablename
group by ID