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.
Order by Score in descending order, and by SubmitDate in ascending order, then use a variable to give correlative numbers to each row:
mysql> SET @Rank := 0;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT *, @Rank := @Rank + 1 AS Rank FROM ranking ORDER BY Score DESC, SubmitDate ASC;
+----+-------+-------+---------------------+------+
| Id | Name | Score | SubmitDate | Rank |
+----+-------+-------+---------------------+------+
| 4 | Bash | 102 | 2014-08-04 21:37:17 | 1 |
| 1 | Ida | 100 | 2014-08-01 21:37:17 | 2 |
| 2 | Boo | 99 | 2014-08-02 21:37:17 | 3 |
| 3 | Lala | 99 | 2014-08-03 21:37:17 | 4 |
| 5 | Assem | 99 | 2014-08-05 21:37:17 | 5 |
+----+-------+-------+---------------------+------+
5 rows in set (0.00 sec)
Best Answer
gives this list:
Getting a single person score:
Gives this result:
You'll have one scan to get the score list, and another scan or seek to do something useful with it. An index on the
score
column would help performance on large tables.