How to find top 3 topper of each subject in given table

id  - Name - Subject -  Marks
1   - ABC  - MAT    -  90
2   - ABC  - SCI    -  80
3   - ABC  - ENG    -  90
4   - ABC  - HIS    -  96
5   - ABC  - PHY    -  70
6   - ABC  - CHE    -  43
7   - XYZ  - MAT    -  90
8   - XYZ  - SCI    -  80
9   - XYZ  - ENG    -  90
10  - XYZ  - HIS    -  96
11  - XYZ  - PHY    -  70
13  - XYZ  - CHE    -  43

etc .....

Just want to show 3 topper of each subject

ABC - MATH - 90
XYZ - MATH - 90
DEF - MATH - 80
etc

Answer

You can do this using variables.

select t.*
from (select t.*,
             (@rn := if(@s = subject, @rn + 1,
                        if(@s := subject, 1, 1)
                       )
             ) as rn
      from t cross join
           (select @rn := 0, @s := '') params
      order by subject, marks desc
     ) t 
where rn <= 3
order by t.subject, t.rn;