CODEWITHSUNDEEP
mysqlsql-order-by
Wayne Rooney
Wayne Rooney

Reputation: 1607

How to order the rows with respect to multiple columns in mysql

How to order the rows with respect to multiple columns such that the ordering maintains the condition that if two people have the same details they get the same rank. I will give an example to illustrate :Here firstly ordering is done by score and in case of tie with penalty and still if tie exist both are given same rank and next person gets the adjusted rank.

###################
rank roll score penalty
 1    11    3     23
 2    12    3     20
 2    13    3     20
 2    14    3     20
 5    15    2     10

So the question is how to fill the rank column??If not possible in mysql what is the other alternative??

Upvotes: 0

Views: 417

Answers (2)

fancyPants
fancyPants

Reputation: 51868

Test data:

/*
drop table test;
create table test (roll int, score int, penalty int);
insert into test (roll, score, penalty) values (11, 3, 23), (12, 3, 20), (13, 3, 20), (14,3,20), (15, 2, 10);
*/

And here it comes:

ALTER TABLE test ADD COLUMN `rank` int FIRST;
CREATE TEMPORARY TABLE tmp_test LIKE test;
INSERT INTO tmp_test (`rank`, roll, score, penalty)
SELECT cast(q.`rank` as unsigned integer) as `rank`, roll, score, penalty FROM (
SELECT IF(@prev != CONCAT(sq.score, '_', sq.penalty), @rownum:=@rownum2, @rownum) AS rank, 
@prev:=CONCAT(sq.score, '_', sq.penalty), 
@rownum2:=@rownum2 + 1,
sq.*
FROM (
  SELECT
  roll, score, penalty
  FROM
  test
  , (SELECT @rownum:=0, @prev:='', @rownum2:=1) r 
  ORDER BY score DESC, penalty DESC
) sq
) q;

UPDATE test t INNER JOIN tmp_test tt ON t.roll = tt.roll AND t.score = tt.score AND t.penalty = tt.penalty
SET t.rank = tt.rank;
/*optionally...*/    
DROP TABLE tmp_test;

You need to work with a temporary table here, because you can't update the table you are reading from. The temporary table will be automatically deleted when the session ends.

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171371

Try this:

SET @row = 0; 
select min(a.Row) as Rank, s.Roll, s.Score, s.Penalty
from (
    select @row := @row + 1 AS Row, 
        Roll, 
        Score, 
        Penalty
    from Score
    order by Score desc, Penalty desc
) a
inner join Score s on a.Score = s.Score and a.Penalty = s.Penalty
group by Roll, Score, Penalty
order by min(Row)

Upvotes: 0

Related Questions