sql - Mysql RANK is not giving RANK for each category -

sql - Mysql RANK is not giving RANK for each category -

i tried next thing not ranking each category wise. instead giving rank records without considering category. want rank re-occur each category

select rs.section,rs.field1,rs.field2 , @currank := @currank + 1 rank ( select rs.field1,rs2.field2 table rs inner bring together table2 rs2 on rs.field1=rs2.field1 grouping rs.section,rs.field1 )rs, (select @currank := 0) r

actual output: (from mysql output )

category field1 field2 rank male 10 10 1 male 11 10 2 male 12 10 3 .... female 10 10 11 female 11 10 12 female 12 10 13 ....

expected output

category field1 field2 rank male 10 10 1 male 11 10 2 male 12 10 3 .... female 10 10 1 female 11 10 2 female 12 10 3 ....

i assume query working is:

select rs.category, rs.field1, rs.field2, @currank := @currank + 1 rank (select rs.cateogry, rs.field1, rs2.field2 table rs inner bring together table2 rs2 on rs.field1 = rs2.field1 grouping rs.category, rs.field1, rs.field2 ) rs cross bring together (select @currank := 0) vars order cateogry, field1;

or this, field names consistent. need variable specify grouping. next work:

select rs.category, rs.field1, rs.field2, (@currank := if(@c = category, @currank + 1, if(@c := category, 1, 1) ) ) rank (select rs.cateogry, rs.field1, rs2.field2 table rs inner bring together table2 rs2 on rs.field1 = rs2.field1 grouping rs.category, rs.field1, rs.field2 ) rs cross bring together (select @currank := 0, @c := null) vars order cateogry, field1;

note assignment of both variables occurs in 1 statement. of import because mysql not guarantee order of evaluation of expressions in select clause. note explicit utilize of group by. suggest utilize , not depend on group by producing results in particular order (that functionality deprecated in recent version of mysql).

mysql sql