How to get rank based on SUM's?

Question

I have comments table where everything is stored, and i have to SUM everything and add BEST ANSWER*10. I need rank for whole list, and how to show rank for specified user/ID.

Here is the SQL:

   SELECT m.member_id AS member_id, 
          (SUM(c.vote_value) + SUM(c.best)*10) AS total
     FROM comments c
     LEFT JOIN members m ON c.author_id = m.member_id
     GROUP BY c.author_id
     ORDER BY total DESC
    LIMIT {$sql_start}, 20

Answer 1

How about something like this:

SET @rank=0;
SELECT * FROM (
   SELECT @rank:=@rank+1 AS rank, m.member_id AS member_id, 
      (SUM(c.vote_value) + SUM(c.best)*10) AS total
   FROM comments c
   LEFT JOIN members m ON c.author_id = m.member_id
   GROUP BY c.author_id
   ORDER BY total DESC
) as sub
LIMIT {$sql_start}, 20

Answer 2

You may want to check out windowing functions if your MySQL version supports them...

 SELECT m.member_id AS member_id, 
          (SUM(c.vote_value) + SUM(c.best)*10) AS total,
          RANK() OVER (ORDER BY (SUM(c.vote_value) + SUM(c.best)*10)) as ranking
     FROM comments c
     LEFT JOIN members m ON c.author_id = m.member_id
     GROUP BY c.author_id
     ORDER BY total DESC;

Another possibility is this:

 SELECT m.member_id AS member_id, 
          (SUM(c.vote_value) + SUM(c.best)*10) AS total,
          (SELECT count(distinct <column you want to rank by>)
           FROM comments c1
           WHERE c1.author_id = m.member_id) as ranking
     FROM comments c
     LEFT JOIN members m ON c.author_id = m.member_id
     GROUP BY c.author_id
     ORDER BY total DESC;

NB: There's a lot of open questions around this, but the above two techniques are simple methods to determine rankings in general. You'll want to change the above to fit your exact need, as I'm a little fuzzy on what constitutes the rank for a member_id.

Answer 3

SELECT
    @rank:=@rank+1 as rank,
    m.member_id AS member_id, 
    (SUM(c.vote_value) + SUM(c.best)*10) AS total
FROM comments c,
(SELECT @rank:=0) as init
LEFT JOIN members m ON c.author_id = m.member_id
GROUP BY c.author_id
ORDER BY total DESC
LIMIT {$sql_start}, 20

In the solution, ranks are always increasing even if total is the same.