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.
Unless you are e.g. counting or summing the entire contents of a table, aggregate functions will require a group by statement. For example, if you are aggregating by counting the number of departments by employee, you must tell it to group by employee
select empname, count(*)
from t1
group by empname
having count(*) < 37
Best Answer
PROPOSED SOLUTION
YOUR SAMPLE DATA
YOUR SAMPLE DATA LOADED
PROPOSED SOLUTION EXECUTED
PROPOSED SOLUTION FOR TIES (BONUS)
GIVE IT A TRY !!!
NOTE : Why this works
All the department names are unique. I needed to strip off the digits on the far right.
I did that with the innermost subquery
I designed it so that it will strip everything to the right of the first digit in the
deptname. That will handle two-digit or three-digit numbers indeptname. After doing so, you end with two distinct values:salesandoperations. I used this as the department type, which is aliased asdepttype.I could then iterate using local variables on the current
depttype. I would also reset the ranking back to 1 when thedepttypechanges while iterating. The ranking technique I used is borrowed from the algorithm that I posted in Get the rank of a user in a score table. I implemented it to mechanically group bydepttypeand rank byhours.