Ranking with ties and conditions

Question

I have the following code to calculate ranking with ties. In this code ranking is calculated from sum of Mark_score. What I want to know is: how can we calculating ranking from mark_scored in each subject. There may be a time when one student fails in just one subject but his sum of mark_scored is more than that of the other student who passed in all the subjects. So in such case, the one who passed in all the subjects must rank higher than the one who get more total mark-score. This code is what I have been trying so far and have no idea how I could achieve what I want. I am searching in google and may be all the available sites in the world that give at least some tutorials in mysql but could not find the solution. Please help me. Php solution or any other suggestion is also welcome. See my code:

SELECT regd, Roll_no, Name_of_Student, Section, Test_date,
       English,
       Mizo,
       Mathematics,
       SS,
       Science,
       score, fmscore, perc, Rank 
FROM 
(
  SELECT t.*, IF(@p = score, @n, @n := @n + 1) AS Rank, @p := score 
    FROM
  (
    SELECT regd, Roll_no, Name_of_Student, Test_date, Section,
            SUM(IF(Subject = 'English'    , Mark_score, 0)) English,
            SUM(IF(Subject = 'Mizo'       , Mark_score, 0)) Mizo,
            SUM(IF(Subject = 'Mathematics', Mark_score, 0)) Mathematics, 
            SUM(IF(Subject = 'SS'         , Mark_score, 0)) SS,
            SUM(IF(Subject = 'Science'    , Mark_score, 0)) Science,
            SUM(Full_mark) fmscore,
            SUM(Mark_score) score,
            SUM(Mark_score) / SUM(Full_mark) * 100 perc 
      FROM exam, (SELECT @n := 0, @p := 0) n 
     GROUP BY regd 
     ORDER BY score DESC
  ) t
) r

This is my table :

  CREATE TABLE IF NOT EXISTS `exam` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `regd` int(11) NOT NULL,
 `Name_of_Student` varchar(100) COLLATE latin1_general_ci NOT NULL,
 `Class` varchar(50) COLLATE latin1_general_ci NOT NULL,
 `Roll_no` int(11) NOT NULL,
 `Section` varchar(50) COLLATE latin1_general_ci NOT NULL,
 `Name_of_exam` varchar(100) COLLATE latin1_general_ci NOT NULL,
 `Test_date` date NOT NULL,
 `Subject` varchar(50) COLLATE latin1_general_ci NOT NULL,
 `Full_mark` int(11) NOT NULL,
 `Mark_score` int(11) NOT NULL,
 `Year` year(4) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=505 ;

Sample Data:

INSERT INTO `exam` (`id`, `regd`, `Name_of_Student`, `Class`, `Roll_no`, `Section`, `Name_of_exam`, `Test_date`, `Subject`, `Full_mark`, `Mark_score`, `Year`) VALUES
(1, 2147, 'Lalrinawmi', 'IX', 1, 'A', 'First Term Unit Test', '2013-07-23', 'English', 30, 28, 2013),
(2, 2149, 'R.Lalruatfela', 'IX', 2, 'A', 'First Term Unit Test', '2013-07-23', 'English', 30, 23, 2013),
(3, 2152, 'Lallawmtlinga', 'IX', 3, 'A', 'First Term Unit Test', '2013-07-23', 'English', 30, 25, 2013),
(4, 2153, 'Malsawmdawngzela', 'IX', 4, 'A', 'First Term Unit Test', '2013-07-23', 'English', 30, 23, 2013),
(5, 2154, 'Lincoln Lalduhsaka', 'IX', 5, 'A', 'First Term Unit Test', '2013-07-23', 'English', 30, 22, 2013),
(6, 2157, 'Vanlalhriatpuia Hrahsel', 'IX', 6, 'A', 'First Term Unit Test', '2013-07-23', 'English', 30, 21, 2013),
(7, 2158, 'C.Malsawmtluanga', 'IX', 7, 'A', 'First Term Unit Test', '2013-07-23', 'English', 30, 23, 2013),
(8, 2162, 'C.M.S. Dawngliana', 'IX', 8, 'A', 'First Term Unit Test', '2013-07-23', 'English', 30, 26, 2013),
(9, 2165, 'B. Malsawmkimi', 'IX', 9, 'A', 'First Term Unit Test', '2013-07-23', 'English', 30, 21, 2013),
(10, 2164, 'Lalnunmawii', 'IX', 10, 'A', 'First Term Unit Test', '2013-07-23', 'English', 30, 20, 2013),
(11, 2167, 'Hmangaihenlaii', 'IX', 11, 'A', 'First Term Unit Test', '2013-07-23', 'English', 30, 29, 2013),
(12, 2170, 'Lalrinsangi', 'IX', 12, 'A', 'First Term Unit Test', '2013-07-23', 'English', 30, 26, 2013),
(13, 2166, 'John Malsawmtluanga', 'IX', 13, 'A', 'First Term Unit Test', '2013-07-23', 'English', 30, 27, 2013),
(14, 2171, 'Malsawmtluangi', 'IX', 14, 'A', 'First Term Unit Test', '2013-07-23', 'English', 30, 26, 2013),
(15, 2173, 'Azid Ahmed Barbhuiya', 'IX', 15, 'A', 'First Term Unit Test', '2013-07-23', 'English', 30, 23, 2013),
(16, 2174, 'John Lalramdina', 'IX', 16, 'A', 'First Term Unit Test', '2013-07-23', 'English', 30, 26, 2013),
(17, 2175, 'B. Lalrotlinga', 'IX', 17, 'A', 'First Term Unit Test', '2013-07-23', 'English', 30, 21, 2013),
(18, 2176, 'Rebec Lalremruati', 'IX', 18, 'A', 'First Term Unit Test', '2013-07-23', 'English', 30, 23, 2013),
(19, 2177, 'Hmingthanmawii', 'IX', 19, 'A', 'First Term Unit Test', '2013-07-23', 'English', 30, 21, 2013),
(20, 2178, 'Johny Lalbiakzuala', 'IX', 20, 'A', 'First Term Unit Test', '2013-07-23', 'English', 30, 23, 2013),
(21, 2179, 'Lalrinfela', 'IX', 21, 'A', 'First Term Unit Test', '2013-07-23', 'English', 30, 21, 2013),
(22, 2181, 'Lalremruata', 'IX', 22, 'A', 'First Term Unit Test', '2013-07-23', 'English', 30, 21, 2013),
(23, 2182, 'Isaac Lalmuankima', 'IX', 23, 'A', 'First Term Unit Test', '2013-07-23', 'English', 30, 20, 2013),
(24, 2185, 'Lalduhsaka', 'IX', 24, 'A', 'First Term Unit Test', '2013-07-23', 'English', 30, 20, 2013),
(25, 2186, 'Lalrinfeli', 'IX', 25, 'A', 'First Term Unit Test', '2013-07-23', 'English', 30, 20, 2013),
(26, 2187, 'Lalnunhlui', 'IX', 26, 'A', 'First Term Unit Test', '2013-07-23', 'English', 30, 20, 2013),
(27, 2188, 'Liantinthanga', 'IX', 27, 'A', 'First Term Unit Test', '2013-07-23', 'English', 30, 21, 2013),
(28, 2190, 'C.Lalmuanpuii', 'IX', 28, 'A', 'First Term Unit Test', '2013-07-23', 'English', 30, 21, 2013),
(29, 2193, 'Lalramzailawma', 'IX', 29, 'A', 'First Term Unit Test', '2013-07-23', 'English', 30, 23, 2013),
(30, 2194, 'Malsawmpuia', 'IX', 30, 'A', 'First Term Unit Test', '2013-07-23', 'English', 30, 26, 2013);

For finding rank in one subject:

SELECT regd, Roll_no, Name_of_Student, Subject,
   score, Rank 
FROM 
(
  SELECT t.*, IF(@p = score, @n, @n := @n + 1) AS Rank, @p := score 
  FROM
  (
   SELECT regd, Roll_no, Name_of_Student, Subject,
        SUM(Mark_score) score
  FROM exam, (SELECT @n := 0, @p := 0) n
  Where Subject='English' 
 GROUP BY regd 
 ORDER BY score DESC
   ) t
) r

Answer 1

What you want is to sort the output using a custom compare function. Do all students who passed all classes rank higher than any student who failed a subject? This will matter in your compare function code.

This sorting with a custom compare function can be done in PHP. However, if you can write the function in your local SQL dialect, it may well be faster than in PHP.