How can I add a rank column based on an aggregated column in mysql?

Question

I want to add a rank column indicating the rank of another numerical column in an SQL query using MySQL. For simple, non aggregating queries this can be achieved using a counter variable as follows:

SELECT value, @i:=@i+1 as rank FROM table, (SELECT @i:=0) rank_init ORDER BY value DESC

This was previously described here and elsewhere on SO.

However, if value is the result of an aggregate function (and the result is therefore ordered by the aggregated values), the rank column seems to be added before the ordering is applied and of course also values are skipped.

SELECT sum(value), @i:=@i+1 as rank FROM table, (SELECT @i:=0) rank_init GROUP BY other_field ORDER BY value DESC

Is there another way without using temporary tables or subqueries?

score 1 · Answer 1 · answered Aug 28 '15 at 15:14

1

Use a subquery:

SELECT value, (@i := @i + 1) as rank
FROM (SELECT sum(t.value) as value
      FROM table 
      GROUP BY other_field
     ) t CROSS JOIN
     (SELECT @i := 0) rank_init
ORDER BY value DESC;

answered Aug 28 '15 at 15:14

Gordon Linoff

1,242,037
58
646
786

Yes, I'm aware of that, as stated in the question I am looking for solutions without subqueries – barbaz Aug 28 '15 at 15:15
I think you need a subquery to accomplish this. I'm also pointing out that just setting a parameter in a query uses a subquery. – Gordon Linoff Aug 28 '15 at 23:28
been looking for something like this, am working on some gamification and it works for me. Thanks – Tadiwanashe Jan 22 '19 at 09:51

How can I add a rank column based on an aggregated column in mysql?

1 Answers1