I echo a rank of a student whose regd is equal to $regd. In fact, this is working code. However, I was advised by a friend that the MySQL statements Distinct and Group By should not be used together. But as a newbie, I could not figure out how I could implement it without using Distinct because it does not return rows without Distinct.

Can anyone suggest how I can improve this code?

mysql_select_db($database_dbconnect, $dbconnect);
$query_myrank = "SELECT Distinct regd, Name_of_exam, 
                    Name_of_Student, TOTALSCORE, Rank 
             FROM (SELECT *, IF(@marks = (@marks := TOTALSCORE), 
                          @auto, @auto := @auto + 1) AS Rank 
                   FROM (SELECT Name_of_Student, regd, 
                               Name_of_exam, SUM(Mark_score) AS TOTALSCORE 
                         FROM cixexam, (SELECT @auto := 0, 
                                               @marks := 0) AS init 
                                        GROUP BY regd 
                         ORDER BY TOTALSCORE DESC) t) AS result 
             HAVING (Name_of_exam='First Terminal Exam' OR 
                     Name_of_exam='First Term Test')";

$myrank = mysql_query($query_myrank, $dbconnect) or die(mysql_error());

$i = 0;
$j = 0;
$data = array();
while($row_myrank = mysql_fetch_assoc($myrank))
   $data[$i] = $row_myrank;
   if(isset($data[$i - 1]) 
        && $data[$i - 1]['TOTALSCORE'] == $data[$i]['TOTALSCORE'])
   $data[$i]['Rank'] = $j;
     $data[$i]['Rank'] = ++$j;
foreach($data as $key => $value)
 if($value['regd'] == $regd)
    echo $value['Rank'];
  • \$\begingroup\$ Why are you using HAVING instead of WHERE? Also, I think you probably intend to filter by Name_of_exam within the innermost query, rather than at the outermost query. \$\endgroup\$ Commented Aug 24, 2013 at 15:26

1 Answer 1


Since you use it in a subquery, I don't see the problem. If you used both in the same query, that would be redundant. But the subquery needs to use it to obtain the required data.

Did the person give a reason as to why he/she thinks this, or was it simply a general statement?

The only thing I can think of is that DISTINCT is rather slow compared to GROUP BY: stackoverflow.com/questions/1887953/

  • \$\begingroup\$ Can you suggest an alternative solution? \$\endgroup\$ Commented Jul 25, 2013 at 14:24
  • \$\begingroup\$ Sadly not, for I was very surprised when you said you're a "newbie", because that's some good code you got there. Intermediate to advanced, I would say. Certainly not "newbie". \$\endgroup\$
    – KoenDG
    Commented Jul 25, 2013 at 15:13
  • \$\begingroup\$ Thanks for your encouragement. I have been struggling with this code for a week. \$\endgroup\$ Commented Jul 25, 2013 at 15:36
  • \$\begingroup\$ I find it very confusing that Name_of_Student, regd, Name_of_exam are selected, but the GROUP BY clause only contains regd. I know that MySQL supports this, but I've never been able to wrap my head around what is going on. What happens if Name_of_Student or Name_of_exam aren't functionally dependent on regd? \$\endgroup\$ Commented Jul 25, 2013 at 22:33
  • \$\begingroup\$ Now I found out: sqlfiddle.com/#!2/bb1252/1 - the non-FD fields seems to be picked at random. For this reason alone, I suggest including the fields in the GROUP BY clause (if they are FD) or specifying an aggregate in stead (MAX seems to work on chars). \$\endgroup\$ Commented Jul 25, 2013 at 22:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.