1
\$\begingroup\$

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?

<?php 
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;
   }else{
     $data[$i]['Rank'] = ++$j;
}
   $i++;
}
foreach($data as $key => $value)
{
 if($value['regd'] == $regd)
    {
    echo $value['Rank'];
    }
  }
 ?>    
\$\endgroup\$
  • \$\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\$ – 200_success Aug 24 '13 at 15:26
1
\$\begingroup\$

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/

|improve this answer|||||
\$\endgroup\$
  • \$\begingroup\$ Can you suggest an alternative solution? \$\endgroup\$ – Lalhriatpuii Mapuii Jul 25 '13 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 Jul 25 '13 at 15:13
  • \$\begingroup\$ Thanks for your encouragement. I have been struggling with this code for a week. \$\endgroup\$ – Lalhriatpuii Mapuii Jul 25 '13 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\$ – Michael Zedeler Jul 25 '13 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\$ – Michael Zedeler Jul 25 '13 at 22:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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