CODEWITHSUNDEEP
mysqlviewrank
Noel
Noel

Reputation: 10525

Assiging rank in mySQL and VIEW creation

I have a table called standings which has points, goal difference and goals scored by each team. Here's the table structure and the data.

CREATE TABLE standings (
  team_id int(3) unsigned NOT NULL AUTO_INCREMENT,
  points int(2) unsigned DEFAULT 0,
  goal_difference int(2) unsigned DEFAULT 0,
  goals_for int(2) unsigned DEFAULT 0,
  PRIMARY KEY (team_id)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;

insert into standings(team_id,points,goal_difference,goals_for) values (1,20,2,17);
insert into standings(team_id,points,goal_difference,goals_for) values (2,14,8,15);
insert into standings(team_id,points,goal_difference,goals_for) values (3,9,2,11);
insert into standings(team_id,points,goal_difference,goals_for) values (4,14,10,12);
insert into standings(team_id,points,goal_difference,goals_for) values (5,17,10,19);
insert into standings(team_id,points,goal_difference,goals_for) values (6,5,-11,7);
insert into standings(team_id,points,goal_difference,goals_for) values (7,14,10,10);
insert into standings(team_id,points,goal_difference,goals_for) values (8,9,2,14);
insert into standings(team_id,points,goal_difference,goals_for) values (9,12,1,10);
insert into standings(team_id,points,goal_difference,goals_for) values (10,9,2,14);
commit;

I want to sort this table in descending order of points, goal_difference and goals_for and assign rank to each team based on this order. Since mySQL doesn't have RANK functions, after searching this site I came up with this query.

SELECT CASE
          WHEN @prev_value = concat(points,'-',goal_difference,'-',goals_for)
          THEN
             @cur_rank
          WHEN @prev_value := concat(points,'-',goal_difference,'-',goals_for)
          THEN
             @cur_rank := @cur_rank + 1
       END
          AS rank, s.team_id, s.points, s.goal_difference, s.goals_for
  FROM standings s, (SELECT @cur_rank := 0) p, (SELECT @prev_rank := 0) q, (SELECT     @prev_value := NULL) r
 ORDER BY s.points DESC, s.goal_difference DESC, s.goals_for DESC;

So far so good. Now I have two questions.

  1. If you see the result of above query, there is tie between team 8 and 10 for 7th position. So, I want to assign rank no 9 to the next team no 3. How do I do this, without adding any more columns in the query.

  2. I want to create a VIEW using this query. But mySQL doesn't let me create one and gives error, 'View's SELECT contains a variable or parameter'. Please suggest how to create VIEW for this.

    CREATE VIEW view_standings
AS
   SELECT CASE
     WHEN @prev_value = concat(points,'-',goal_difference,'-',goals_for)
     THEN
        @cur_rank
     WHEN @prev_value := concat(points,'-',goal_difference,'-',goals_for)
     THEN
        @cur_rank := @cur_rank + 1
  END
     AS rank,s.team_id,s.points,s.goal_difference,s.goals_for
FROM standings s,(SELECT @cur_rank := 0) p,(SELECT @prev_rank := 0) q,(SELECT @prev_value := NULL) r
ORDER BY s.points DESC, s.goal_difference DESC, s.goals_for DESC;

Upvotes: 2

Views: 942

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can also do rankings using correlated subqueries. If you have even a moderate amount of data, this might prove computationally intensive.

 select s.*,
        (select 1+COUNT(*)
         from standings s2
         where s2.points > s.points or
               (s2.points = s.points and s2.goal_difference > s.goal_difference) or
               (s2.points = s.points and s2.goal_difference = s.goal_difference and s2.goals_for > sys.goals_for
        ) as ranking
 from standings s

Because it only has a subquery in the from clause, you can use this as a view.

I think you can improve performance by having an index no standings(points, goal_difference, goals_for).

Upvotes: 2

Related Questions