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

how order rows respect multiple columns such ordering maintains condition if 2 people have same details same rank. give example illustrate :here firstly ordering done score , in case of tie penalty , still if tie exist both given same rank , next person gets 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 question how fill rank column??if not possible in mysql other alternative??

test data:

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

and here comes:

alter table test add column `rank` int first; create temporary table tmp_test test; insert tmp_test (`rank`, roll, score, penalty) select cast(q.`rank` unsigned integer) `rank`, roll, score, penalty ( select if(@prev != concat(sq.score, '_', sq.penalty), @rownum:=@rownum2, @rownum) rank,  @prev:=concat(sq.score, '_', sq.penalty),  @rownum2:=@rownum2 + 1, sq.* (   select   roll, score, penalty     test   , (select @rownum:=0, @prev:='', @rownum2:=1) r    order score desc, penalty desc ) sq ) q;  update test t inner join tmp_test tt on t.roll = tt.roll , t.score = tt.score , t.penalty = tt.penalty set t.rank = tt.rank; /*optionally...*/     drop table tmp_test; 

you need work temporary table here, because can't update table reading from. temporary table automatically deleted when session ends.