MYSQL score by rank

I am using MYSQL to create a rating system to implement my database. What I want to do is to rate each attribute by its percentage. Here is the example database:

ID, value
1, 3
2, 5
3, 2
4, 5

The output I want is:

ID, value, rank, score
1, 3, 2, 5
2, 5, 1, 10
3, 2, 3, 0
4, 5, 1, 10

score's value will based on the rank so it becomes such as

10*(MAX(rank)-(rank))/(MAX(rank)-MIN(rank))

I have done the rank query but stuck with transforming it into scores. Here is the query I got so far:

SELECT `ID`, `value`, FIND_IN_SET( `value`, (
SELECT GROUP_CONCAT(DISTINCT `value` 
ORDER BY `value` DESC)
FROM table) 
) AS rank
FROM table;

Thank you all guys :)

Another question:

| ID | VALUE1 | RANK1 | VALUE2 | RANK2 | 
----------------------------------------
|  2 |      5 |     1 |     20 |      2|
|  4 |      5 |     1 |     30 |      1|
|  1 |      3 |     2 |      5 |      4|
|  3 |      2 |     3 |      8 |      3|

How to generate multiple ranking like the table? I have tried

SELECT
    @min_rank := 1 AS min_rank
  , @max_rank1 := (SELECT COUNT(DISTINCT value1) FROM table) AS max_rank1
  , @max_rank2 := (SELECT COUNT(DISTINCT value2) FROM table) AS max_rank2
  ;
SELECT
    ID
  , R1
  , TRUNCATE(5.0+5.0 * (@max_rank1 - R1) / (@max_rank1 - @min_rank), 2) AS Score1
  , R2
  , TRUNCATE(5.0+5.0 * (@max_rank2 - R2) / (@max_rank2 - @min_rank), 2) AS Score2
FROM (
  SELECT
      ID
    , value1
    , FIND_IN_SET( `value1`, (SELECT GROUP_CONCAT(DISTINCT `value1` ORDER BY `value1` DESC) FROM table)) AS R1
    , value2
    , FIND_IN_SET( `value2`, (SELECT GROUP_CONCAT(DISTINCT `value2` ORDER BY `value2` DESC) FROM table)) AS R2
  FROM table
) ranked_table;

It works fine with ranking below 170. My database has approximate 200+ ranking for some values and ranks larger then 170 will be seen as 0 when it returns. In that case, some scores will be miscalculated.

Using the techniques described here to rank items in MySQL, you can calculate the scores you need using a derived table and some hackery with user variables.

Here's your table with the sample data you provided:

CREATE TABLE blah (
    id INT
  , value INT
);

INSERT blah (id, value)
VALUES 
    (1, 3)
  , (2, 5)
  , (3, 2)
  , (4, 5)
;

And here's the query you need:

-- find the min and max ranks for later use
SELECT
    @min_rank := 1 AS min_rank
  , @max_rank := (SELECT COUNT(DISTINCT value) FROM blah) AS max_rank
;

-- use the calculated min and max ranks to calculate score
SELECT
    id
  , value
  , rank
  , 10.0 * (@max_rank - rank) / (@max_rank - @min_rank) AS score
FROM (
  SELECT
      id
    , value
    , @curr_rank := IF(@prev_rank = value, @curr_rank, @curr_rank + 1) AS rank
    , @prev_rank := value
  FROM blah, (SELECT @curr_rank := 0) r, (SELECT @prev_rank := NULL) p
  ORDER BY value DESC
) ranked_blah;

The output I get for the second query on SQL Fiddle is as follows:

| ID | VALUE | RANK | SCORE |
-----------------------------
|  2 |     5 |    1 |    10 |
|  4 |     5 |    1 |    10 |
|  1 |     3 |    2 |     5 |
|  3 |     2 |    3 |     0 |

Note that this approach will work best if you have an index on value. This will let you find @max_rank quickly using the COUNT technique @ypercube described.

StackExchange Database Administrators Q#40415, answer score: 2