SQL Query, how to order by date in specific case-CodePudding

I have a table 'exam_table' containing : User_ID, Exam_date, Exam_status.

Exam_status = ['Success' or 'Fail']

The question is :

Based on the above data, propose an SQL query to finds the 5 candidates with the most failures. In case of equality, we wish to obtain first the students whose date of first exam is the most distant in time.

I found the 5 candidates with the most failures but I still don't know how to sort them according to exam_date in case of equality.

Do you have any suggestions? Thank you in advance for helping !

CodePudding user response：

Your order by is a clause which has ordering criteria separated by ,. So you can easily add another criteria, like below:

SELECT User_ID, count(exam_status) as nb_Failures
FROM exam_table
GROUP BY User_ID
ORDER BY nb_Failures, min(exam_date)
LIMIT 5;

CodePudding user response：

UPDATED: corrected by the date of the first exam:

SELECT
    user_id,
    MIN (exam_date) AS first_exam_date,
    SUM (
      CASE exam_status
        WHEN 'Failed' THEN 1
        ELSE 0
      END
    ) AS nb_failures
  FROM exam_table
  GROUP BY user_id
  ORDER BY nb_failures DESC, first_exam_date ASC
  LIMIT 5;

or like this:

SELECT
    user_id,
    MIN (exam_date) AS first_exam_date,
    COUNT(exam_status) AS nb_failures
  FROM exam_table
  WHERE exam_status = 'Failed'
  GROUP BY user_id
  ORDER BY nb_failures DESC, first_exam_date ASC
  LIMIT 5;

PS: aggregate functions must also be applied to the date

PPS: but the first and second queries have different results. In the first, the date of the first exam is selected, in principle, it does not matter if it is successful or not. The second selects only the date of the first failed exam.