tumbin tumbin - 3 months ago 7
MySQL Question

dividing with highest value from mysql cell

i want to give rating to student so need to divide by highest marks achieved by topper is respective subject.
once i want to divide by smallest no also. i dont want to do multiple query.

here is the table

id name physics math
1 Adam 68 80
2 Mark 55 97
3 Luis 78 44
4 Philip 99 62
5 James 59 89


my php code is

$re99 = "SELECT * FROM `tableA` order by id desc limit 10";
while($row2 = $res2->fetch_assoc()){

$name=$row2['name'];
$physics=$row2['physics'];
$math=$row2['math'];

$grade=$physics/99+$math/97;

$poor='poor performer in math is mark with 55 marks and Luis with 44 marks';
}


i dont know how to get these 4 values(99,97,55,44) from above table in one query

Answer

What about select *, max(physics) as high_physics, max(math) as high_math, min(physics) as min_physics, min(math) as min_math from tableA order by id desc limit 10;

Then you can do something like $grade = $physics / $row2['high_physics'] + $math / $row2['high_math'];

Comments