---------------------------------------------------------------------
id | stid | Subject | Total_cumulative | Year |
----------------------------------------------------------------------
1 | 23 | English | 40 | 2014/2015
2 | 1 | English | 29 | 2014/2015
3 | 13 | Maths | 40 | 2014/2015
4 | 4 | Physics | 60 | 2014/2015
5 | 13 | Commerce | 40 | 2014/2015
6 | 1 | Biology | 89 | 2014/2015
7 | 13 | English | 29 | 2014/2015
8 | 13 | Agric | 60 | 2014/2015
Now I have a query that gets all of a particular students results as follow
SELECT * FROM results where stid='13' AND year='2014/2015'
ORDER BY subject ASC LIMIT 20
and I have the following result
---------------------------------------------------------------------
id | stid | Subject | Total_cumulative | Year |
----------------------------------------------------------------------
3 | 13 | Maths | 40 | 2014/2015
5 | 13 | Commerce | 40 | 2014/2015
7 | 13 | English | 89 | 2014/2015
8 | 13 | Agric | 60 | 2014/2015
But my aim is to get the particular students rank in each subject and I have a sub query inside my the while loop of my first sql statement as follow
SELECT rank_number, id, name, total_cumulative
FROM ( SELECT id, name, total_cumulative, @rank:=@rank+1 AS rank_number FROM
(SELECT pl.id, pl.name,SUM(en.total_cumulative) AS total_cumulative
FROM students pl JOIN results en ON pl.id = en.stid
WHERE en.subject = '$subject' AND en.year='$year'
GROUP BY pl.id ORDER BY total_cumulative DESC ) AS rankings,
(SELECT @rank:=0) AS r ) AS overall_rankings
WHERE id = '13'
LIMIT 0, 1";
after that I have the following result
----------------------------------------------------------------------------
id | stid | Subject | Total_cumulative | Year | Rank
----------------------------------------------------------------------------
3 | 13 | Maths | 40 | 2014/2015 | 1
5 | 13 | Commerce | 40 | 2014/2015 | 2
7 | 13 | English | 29 | 2014/2015 | 3
8 | 13 | Agric | 60 | 2014/2015 | 1
but I want the query to check for ties and give the results in the following order
----------------------------------------------------------------------------
id | stid | Subject | Total_cumulative | Year | Rank
----------------------------------------------------------------------------
3 | 13 | Maths | 40 | 2014/2015 | 1
5 | 13 | Commerce | 40 | 2014/2015 | 2
7 | 13 | English | 29 | 2014/2015 | 2
8 | 13 | Agric | 60 | 2014/2015 | 1
Please help me I am a newbies in mysql programming