Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Nice. Since MIN(rank) should be 1 always (am I wrong on this?) and MAX(rank) can be calculated with (SELECT COUNT(DINSTINCT value) FROM blah), you could add this subquery as a derived table and avoid the double scan. It would need one table scan and an index scan on (value) index for the derived table. –  ypercube Apr 19 '13 at 5:45
    
@ypercube - That's correct! I'll update the query. –  Nick Chammas Apr 19 '13 at 6:17
    
Thank all you guys :) It works now –  RyanGrant Apr 19 '13 at 12:11
    
Another question. Is it possible to use this query to run a multiple ranking? For example, value1 and value2 for rank1 and rank2 each. I have tried modifying query but it won't work. –  RyanGrant Apr 23 '13 at 5:24
    
@RyanGrant - I'm not sure what you mean by a multiple ranking. Also, what have you tried already? –  Nick Chammas Apr 23 '13 at 6:20
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.