Rank project numbers in view, MYSQL

Question

I created a view by the following statement.

CREATE VIEW
view_projectHour
AS
SELECT pno
, SUM( hours ) AS total_hours
FROM works_on
GROUP BY pno
ORDER BY total_hours DESC

Now, how can I implement ranking in this view? I want the projects to be ranked. The project with the highest hours must be ranked 1 and be placed on the top and so on. Also there are projects with the same hours.

In other words, there may be ties, but how should the ties be ranked? Ie should a two way tie for first place rank both as #1 with the next as #3? — Joachim Isaksson, Nov 25 '13 at 18:52
Yeah, that would be fine. Either that, or rank both as #1 and the next #2. — Modaresi, Nov 25 '13 at 18:53
I think that you can solve this by solution which @Salman post in another topic: http://stackoverflow.com/a/14297055/780592 — Akatum, Nov 25 '13 at 19:18

peterm · Answer 1 · 2013-11-25T19:50:47.270

2

Unfortunately MySQL lack support for analytic functions. Particularly RANK() and RANK_DENSE().

To emulate RANK() you can do

SELECT pno, total_hours, rank
  FROM
( 
  SELECT pno, total_hours,
         @n := @n + 1 rnum, @r := IF(@h = total_hours, @r, @n) rank, @h := total_hours
    FROM
  (
    SELECT pno, SUM(hours) total_hours
      FROM works_on 
     GROUP BY pno
  ) q CROSS JOIN (SELECT @n := 0, @r := 0, @h := NULL) i
   ORDER BY total_hours DESC, pno
) t

Sample output:

| PNO | TOTAL_HOURS | RANK |
|-----|-------------|------|
|   3 |          61 |    1 |
|   1 |          40 |    2 |
|   2 |          40 |    2 |
|   4 |          10 |    4 |

To emulate DENSE_RANK() you can do

SELECT pno, total_hours, rank
  FROM
(
  SELECT pno, total_hours,
         @r := IF(@h = total_hours, @r, @r + 1) rank, @h := total_hours
    FROM
  (
    SELECT pno, SUM(hours) total_hours
      FROM works_on 
     GROUP BY pno
  ) q CROSS JOIN (SELECT @r := 0, @h := NULL) i
   ORDER BY total_hours DESC, pno
) t

Sample output:

| PNO | TOTAL_HOURS | RANK |
|-----|-------------|------|
|   3 |          61 |    1 |
|   1 |          40 |    2 |
|   2 |          40 |    2 |
|   4 |          10 |    3 |

Note: You can ditch outer SELECTs if you don't mind to have one or two extra columns in your resultset.

Here is SQLFiddle demo

edited Nov 25 '13 at 19:50

answered Nov 25 '13 at 19:29

peterm

91,357
15
148
157

Was looking at the same thing earlier and found this in the MySQL docs; [As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement. ... For other statements, such as SELECT, you might get the results you expect, but this is not guaranteed. ... the order of evaluation for expressions involving user variables is undefined.](http://dev.mysql.com/doc/refman/5.0/en/user-variables.html) – Joachim Isaksson Nov 25 '13 at 19:35
@JoachimIsaksson Yes I know, it's like using MySQL `GROUP BY` extension: nothing is guaranteed but works as expected. It's either that or use `COUNT()` to emulate rank functions or do it on client side. Anyway **thank you** for your input. I agree that at least OP should be aware of it. – peterm Nov 25 '13 at 19:42
Well it sorts correctly, but for rank column it will output "[BLOB - 1 B" which is an error for MySQL returned an empty result set. – Modaresi Nov 25 '13 at 19:46
Please take a look at working sqlfiddle demo. I provided a link in the answer. It should work just fine. – peterm Nov 25 '13 at 19:52

score 1 · Accepted Answer · answered Nov 25 '13 at 19:51

An alternate solution is to use a JOIN to count how many values are ranked better for each row;

SELECT 1+COUNT(b.total_hours) rank, a.pno, a.total_hours
FROM test a
LEFT JOIN test b
  ON a.total_hours < b.total_hours
GROUP BY a.pno, a.total_hours
ORDER BY total_hours DESC;

An SQLfiddle to test with.

Rank project numbers in view, MYSQL

2 Answers2