Ranking a table through multiple categories

Question

I am trying to find a way to rank people's scores through multiple categories. The table contains a list of every player along with the event they participated in, and their score.

The query needs to assign a rank to each player based on score within each event. (The Ranks assigned in Event 1 and Event 2 should be completely separate.)

If the player is missing from an event, they should be assigned a Rank of (Total players in that category + 1)

I can do a basic ranking over 1 category. That's simple. But I don't even have a clue how to do this.

Data in table

+--------+-------+---------+
| Name   | Score | Event # |
+--------+-------+---------+
| Kevin  | 73    | Event 1 |
| George | 69    | Event 1 |
| Henry  | 70    | Event 1 |
|        |       |         |
| George | 45    | Event 2 |
| Kevin  | 65    | Event 2 |
| Henry  | 65    | Event 2 |
| Daniel | 50    | Event 2 |
+--------+-------+---------+

Expected return

+---------+--------+------+
| Event # | Name   | Rank |
+---------+--------+------+
| Event 1 | Kevin  | 1    |
| Event 1 | Henry  | 2    |
| Event 1 | George | 3    |
| Event 1 | Daniel | 4    |
| Event 2 | Kevin  | 1    |
| Event 2 | Henry  | 1    |
| Event 2 | Daniel | 3    |
| Event 2 | George | 4    |
+---------+--------+------+

Note the ranking is NOT dense! I am on MySql 5.7.

In actuality I have over 50 events and hundreds of people.

This is what I have for ranking 1 event

SELECT
        IF( SCORE =@_last_rank,@curRank:=@curRank,@curRank:=@_sequence) AS RANK,
        NAME,
        EVENT,
        @_sequence:=@_sequence+1,@_last_rank:= SCORE
        FROM (SELECT * FROM database WHERE EVENT = $event) p, 
        (SELECT @curRank := 1, @_sequence:=1, @_last_rank:=0) r
        ORDER BY  RANK, NAME

"I can do a basic ranking over 1 category. That's simple." - Please code the corresponding code. — Paul Spiegel, Jul 13 '19 at 12:01
Do you have a master table for all the "Name" ? Also check this answer, for general approach: https://stackoverflow.com/a/53465139/2469308 — Madhur Bhaiya, Jul 13 '19 at 12:02
Where are you handling "If the player is missing from an event, they should be assigned a Rank of (Total players in that category + 1)" in your code? — Paul Spiegel, Jul 13 '19 at 12:20
I can't figure that part out. In my description I mentioned I could do basic ranking. This is as far as I could get. I'm still very new to SQL @PaulSpiegel — royco, Jul 13 '19 at 12:21
Your query is [not doing](https://www.db-fiddle.com/f/nTbu2GfAoihjDRyWbXcTdS/0) what you want. For the start you need [ORDER BY in the subquery](https://www.db-fiddle.com/f/bcTS1PZMGD8HSBrCFRt1nx/0). — Paul Spiegel, Jul 13 '19 at 12:49
You should really consider to solve that in application code or switch to a new version with window functions. When I see the common solutions with user variables, my eyes hurt. — Paul Spiegel, Jul 13 '19 at 13:53

score 0 · Answer 1 · answered Jul 13 '19 at 12:50

This sql statement

Select eventno,Name,rank 
From 
 (Select eventno,Name,
            CASE 
              WHEN @event = eventno THEN @rownum := @rownum + 1
              ELSE 
                  @rownum := 1
             END as rank,
             @event := eventno
      From table1 t
      JOIN (SELECT @rownum := 0) r
      JOIN (SELECT @event := (Select Eventno from table1 Limit 1)) s
  ORDER BY t.eventno,t.score) x;


  CREATE TABLE table1
  (`Name` varchar(6), `Score` int, `Eventno` varchar(7)) 

  INSERT INTO table1
(`Name`, `Score`, `Eventno`)
VALUES
('Kevin', 73, 'Event 1'),
('George', 69, 'Event 1'),
('Henry', 70, 'Event 1'),
('George', 45, 'Event 2'),
('Kevin', 65, 'Event 2'),
('Henry', 65, 'Event 2'),
('Daniel', 50, 'Event 2')

Gives following result:

  eventno   Name    rank
  Event 1   George  1
  Event 1   Henry   2
  Event 1   Kevin   3
  Event 2   George  1
  Event 2   Daniel  2
  Event 2   Kevin   3
  Event 2   Henry   4
  7 rows

score 0 · Answer 2 · answered Jul 13 '19 at 18:01

The following approach avoids the (non reriable) use of user variables and highly complex queries. It is using temporary tables and utilizing MyISAM's AUTO_INCREMENT per group feature, to generate row numbers per group. To get the rank from the row numbers a GROUP BY query with MIN(row_number) is used.

drop temporary table if exists tmp_scores;
create temporary table tmp_scores(
  rn int auto_increment,
  event varchar(50),
  name varchar(50),
  score int,
  primary key (event, rn)
)engine=myisam;

insert into tmp_scores(event, name, score)
  select e.event, n.name, t.score
  from (select distinct t.event from mytable t) e
  cross join (select distinct t.name from mytable t) n
  left join mytable t on t.event = e.event and t.name = n.name
  order by e.event, t.score is null, t.score desc;

drop temporary table if exists tmp_rank_by_score;
create temporary table tmp_rank_by_score(
  event varchar(50),
  score int,
  rank int,
  primary key (event, rank)
)engine=myisam;

insert into tmp_rank_by_score(event, score, rank)
  select event, score, min(rn) as rank
  from tmp_scores
  group by event, score;

select s.event, s.name, s.score, r.rank
from tmp_scores s
join tmp_rank_by_score r on r.event = s.event and r.score <=> s.score
order by r.event, r.rank;

drop temporary table if exists tmp_rank_by_score;
drop temporary table if exists tmp_scores;

Result:

| event   | name   | score | rank |
| ------- | ------ | ----- | ---- |
| Event 1 | Kevin  | 73    | 1    |
| Event 1 | Henry  | 70    | 2    |
| Event 1 | George | 69    | 3    |
| Event 1 | Daniel |       | 4    |
| Event 2 | Kevin  | 65    | 1    |
| Event 2 | Henry  | 65    | 1    |
| Event 2 | Daniel | 50    | 3    |
| Event 2 | George | 45    | 4    |

db-fiddle

Ranking a table through multiple categories

2 Answers2