CODEWITHSUNDEEP
phpmysql
user3396245
user3396245

Reputation: 11

MAX function not working

I have this table :

 fistname      | lastname     |  english 
--------------------------------------------- 
bob           | wilson       | 77  
jess          | farr         | 47

And this is my code in php :

    $con = mysql_connect('localhost','root','');    
mysql_select_db('education');
$highest= mysql_query("SELECT firstname, lastname, max(english)FROM students");
$row = mysql_fetch_row($highest);
echo 'English Highscore:' ;
echo "\r\n" ;
echo $row[0];
echo '&nbsp';
echo $row[1];

It will display jess as the one with the high score and not bob:

Upvotes: 1

Views: 48

Answers (3)

Mubo
Mubo

Reputation: 1070

Try this :-

SELECT firstname, lastname, english
FROM students
ORDER BY english DESC
LIMIT 1

Upvotes: 0

Fabio
Fabio

Reputation: 23480

When you use aggregate function you need to use GROUP BY clause

SELECT firstname, lastname, MAX(english)
FROM students
GROUP BY lastname, firstname

From documentation

  • If you use a group function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows.

Upvotes: 1

poostchi
poostchi

Reputation: 422

Try this:

$highest= mysql_query("SELECT firstname, lastname, english FROM students order by english DESC limit 1");

Upvotes: 2

Related Questions