Ranking with Group by Weekly Rank

Question

I am trying to get a weekly ranking type result out of my dataset, and failing with multiple techniques.

My data looks like this:

+------------+------+--------+
| team       | week |points  |
+------------+------+--------+
| blue       |   1  | 10     | 
| green      |   1  | 15     |
| red        |   1  | 16     |
| blue       |   2  | 5      |
| green      |   2  | 18     |
| red        |   2  | 6      |
| blue       |   3  | 2      |
| green      |   3  | 8      |
| red        |   3  | 8      |
+------------+------+--------+

And I am trying to get something like this:

+------------+------+--------+
| team       | week | rank   | 
+------------+------+--------+
| blue       |   1  | 3      | 
| green      |   1  | 2      |
| red        |   1  | 1      |
| blue       |   2  | 3      |
| green      |   2  | 1      |
| red        |   2  | 2      |
| blue       |   3  | 3      |
| green      |   3  | 1      |
| red        |   3  | 1      |
+------------+------+--------+

You can see in week 3 that points are tied, so the rank needs to be tied as well. The tied rank will always be the lower of the values. (i.e joint 2nd will always rank 2 2 not 3 3).

It can be a stored procedure, view, anything. But it needs to be MySql.

I have this so far:

SELECT team, 
@rnk:= CASE 
    WHEN @week_id <> week THEN 0
    WHEN points = @tot THEN @rnk
    ELSE @rnk+1 
END 
AS rank,
@week_id := week AS wk,
@tot := points AS pts
FROM
  (SELECT @rnk:= -1) s,
  (SELECT @week_id:= -1) c,
  (SELECT *
   FROM v_weeklypointtotals
   ORDER BY week, points DESC
  ) t

However, it works great when called from MySql workbench. But when called via PHP it gives incorrect results. (It doesn't take into account the tied weeks). I think this may have something to do with the order the variables are set... but I am stumped.

You should have a look at old post in SO. See one here- http://stackoverflow.com/questions/3333665/mysql-rank-function — DhruvJoshi, Feb 10 '14 at 09:05

score 3 · Accepted Answer · answered Feb 10 '14 at 09:58

3

Try this:

select team,week,
(select count(*)+1 from Table1 where week=t1.week
                                and points >t1.points ) as rank
FROM Table1 t1

SQLFiddle demo

answered Feb 10 '14 at 09:58

valex

23,966
7
43
60

That is 100% simple, and spot on. Thanks. – djangoat Feb 10 '14 at 10:03

score -1 · Answer 2 · answered Feb 10 '14 at 09:11

-1

BTW if you did not bother looking into the SO post i linked in comment you can try this wizardry :)

SELECT    ( 
            CASE Week 
            WHEN @curW 
            THEN @curRow := @curRow + 1 
            ELSE @curRow := 1 AND @curW := Week END
          ) + 1 AS rank,
          Week,
          team,
          points
FROM      tablename,
          (SELECT @curRow := 0, @curW := 0) r
ORDER BY  Week DESC, points DESC;

answered Feb 10 '14 at 09:11

DhruvJoshi

17,041
6
41
60

It doesn't take account of the tied situations – djangoat Feb 10 '14 at 09:32

Ranking with Group by Weekly Rank

2 Answers2