Judson Judson - 3 months ago 20
MySQL Question

Ranking Results from MySql

I am trying to rank results, firstly based on total, but I need to break ties based on the date of the last entry. Below is the query I have that works for ranking, but does not handle ties.

SELECT @rownum := @rownum +1 AS rank, total, user_id
FROM (
SELECT sum( score ) AS total, user_id
FROM submissions
WHERE group_id = $group_id
AND status = 1";
GROUP BY user_id
ORDER BY total DESC
) T, (
SELECT @rownum :=0
) a


This returns the following

rank | total | user_id
1 | 90 | 1366
2 | 89 | 756
3 | 89 | 1026
4 | 89 | 1987


What I need is another sub query to get each users submissions (or at least the last submission) and make it rank by the earliest date_submitted.

Answer

The last submission per user would probably have the latest (max) date, therefore you can use the max() function to retrieve it in the subquery:

SELECT @rownum := @rownum +1 AS rank, total, user_id
FROM (
    SELECT sum( score ) AS total, max(your_date_field) as last_submission_time, user_id
    FROM submissions
    WHERE group_id = $group_id
    AND status = 1";
    GROUP BY user_id
        ORDER BY total DESC, last_submission_time ASC
) T, (
   SELECT @rownum :=0
) a