CODEWITHSUNDEEP
mysqlsubquery
Judson
Judson

Reputation: 107

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.

Upvotes: 0

Views: 119

Answers (1)

Shadow
Shadow

Reputation: 34232

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

Upvotes: 2

Related Questions