CODEWITHSUNDEEP
mysqlrank
Finlay Evans
Finlay Evans

Reputation: 1

How to sort a table on multiple ranks

I have a problem regarding creating and sorting a leader-board based on a single table of results. Here is a simple example of what I'm trying to do;

table results;
 Name RaceNo Time
 Sam   1     34 
 Ben   1     27
 Sam   2     29
 Ken   1     30
 Ben   2     32
 Sam   3     35
 Ken   2     33
 Ken   3     38
 Ben   3     33

RaceNo = up to 16 or so; Names = up to 50 or so;

I want to get sorted output as follows; output with rows of races, sorted by Total 

 Name R1 R2 R3 Total(sum R1-n)
 Ben  27 32 33   92
 Sam  34 29 35   98
 Ken  30 33 38  101

What I really want is; Desired output is each race ranked 1-n, and sum(Rank) with rows sorted by sum(Rank). 

 Name Rnk1 Rnk2 Rnk3 Total(sum Rank1-n)
 Ben   1    2    1    4
 Sam   3    1    2    6
 Ken   2    3    3    8

I have no idea how to approach this. The following will rank a single race, but I don't know how to string all races together into a single query.

select Name, find_in_set( Time, (select group_concat(Time order by Time) from 
results where RaceNo=1)) as rank from results where RaceNo=1;

This is still a long way from what I want to do. I'm considering looping round in PHP, but I'm sure there is a better way to do it in MySQL. I'd be very grateful if anyone could help?

Upvotes: 0

Views: 123

Answers (2)

Adam
Adam

Reputation: 18807

you already have done the biggest part:

This way, you can have all the ranks, in a flat column

  SELECT RaceNo, Name,
       FIND_IN_SET( Time,
              (SELECT GROUP_CONCAT(Time order by Time)
                   FROM results WHERE RaceNo=m1.RaceNo)) as rank 
  FROM results m1;

You just have to get one step further to get a specific race:

  SELECT m2.Name, SUM(IF(m2.RaceNo=1, m2.rank, 0)) R1
  FROM (SELECT RaceNo, Name, FIND_IN_SET( Time,
                (SELECT GROUP_CONCAT(Time order by Time)
                 FROM results 
                 WHERE RaceNo=m1.RaceNo)) as rank
        FROM results m1) m2 GROUP BY m2.Name;

And so you can generate your query for all races doing the following

  ## generating the different columns
  SELECT CONCAT(GROUP_CONCAT(
            CONCAT("SUM(IF(m2.RaceNo=", RaceNo, ", m2.rank, 0)) R", RaceNo)),
            ", SUM(m2.rank) Total")
    INTO @query
    FROM (SELECT DISTINCT RaceNo FROM results ) m;

  ## inserting the columns in our previous request
  SELECT CONCAT("SELECT m2.Name, ", @query,
               " FROM (SELECT RaceNo, Name, FIND_IN_SET( Time,
                           (SELECT GROUP_CONCAT(Time order by Time)
                            FROM results WHERE RaceNo=m1.RaceNo)) as rank
                       FROM results m1) m2 GROUP BY m2.Name;")
    INTO @query;

  ## execute it as a statement
  mysql> PREPARE stmt FROM @query;
  mysql> EXECUTE stmt;
  +------+------+------+------+-------+
  | Name | R1   | R2   | R3   | Total |
  +------+------+------+------+-------+
  | Ben  |    1 |    2 |    1 |     4 |
  | Ken  |    2 |    3 |    3 |     8 |
  | Sam  |    3 |    1 |    2 |     6 |
  +------+------+------+------+-------+
  3 rows in set (0.00 sec)

Upvotes: 1

Raging Bull
Raging Bull

Reputation: 18737

For first result, 

SELECT Name,
       MAX(CASE WHEN RaceNo=1 THEN Time END) AS R1,
       MAX(CASE WHEN RaceNo=2 THEN Time END) AS R2,
       MAX(CASE WHEN RaceNo=3 THEN Time END) AS R3,
       IFNULL(MAX(CASE WHEN RaceNo=1 THEN Time END),0)+
       IFNULL(MAX(CASE WHEN RaceNo=2 THEN Time END),0)+
       IFNULL(MAX(CASE WHEN RaceNo=3 THEN Time END),0) AS Total
FROM TableName
GROUP BY Name

Result:

NAME    R1  R2  R3  TOTAL
Ben     27  32  33  92
Ken     30  33  38  101
Sam     34  29  35  98

See result in SQL Fiddle.

Upvotes: 0

Related Questions