0
---------------------------------------------------------------------
   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

Tushar
  • 3,527
  • 9
  • 27
  • 49
Amacyber
  • 1
  • 1
  • if I understand your problem correctly then solution for your problem might be here http://stackoverflow.com/questions/3614666/mysql-get-row-position-in-order-by – Aleksandar Miladinovic Apr 01 '15 at 13:55
  • Readers answering this question may first wish to check the OP's ranking SQL questions: [one](http://stackoverflow.com/q/29388723), [two](http://stackoverflow.com/q/29391651), [three](http://stackoverflow.com/q/29451275), [four](http://stackoverflow.com/q/29452337) and [five](http://stackoverflow.com/q/29486804), so that work is not accidentally duplicated. – halfer Apr 07 '15 at 08:48

0 Answers0