Re: QUERY rank in query result

[kordirko kordirko]

Hello,

try this query

SELECT tab.ID, tab.NAME, tab.SCORE, rnk.rank
FROM test.tab tab
JOIN (
    SELECT SCORE, ROUND( SUM( ID ) / COUNT(*), 1) RANK
    FROM tab
    GROUP BY SCORE
) rnk
ON tab.score = rnk.score
ORDER BY id, name;

+ ------- + --------- + ---------- + --------- +
| ID      | NAME      | SCORE      | rank      |
+ ------- + --------- + ---------- + --------- +
| 1       | A         | 50         | 1.5       |
| 2       | B         | 50         | 1.5       |
| 3       | C         | 49         | 3.0       |
| 4       | D         | 47         | 4.0       |
| 5       | E         | 45         | 5.0       |
| 6       | F         | 43         | 7.0       |
| 7       | G         | 43         | 7.0       |
| 8       | H         | 43         | 7.0       |
+ ------- + --------- + ---------- + --------- +
8 rows

Edited 1 time(s). Last edit at 01/02/2012 03:47PM by kordirko kordirko.