CODEWITHSUNDEEP
phpmysqlsql
Luis Parker
Luis Parker

Reputation: 151

SQL - Return Max Column Value

I have been searching for how to do this but I didn't find anything useful!

So I have the following table with the following columns:

ID | USER | COMMENTS
---------------------
1  | John | 20
2  | Sara | 32
3  | Peter| 10

What I want to do is to pick the user with most comment. I'm using:

<?php
$usermaxresult = mysql_query("SELECT MAX(comments) FROM users"); 
while ($usermaxrow = mysql_fetch_array($usermaxresult)) {
$max = "MAX(comments)";
echo "$usermaxrow[$max]";
}
?>

But that would only return the number of max comments, not the user with the max comments.

---- WORKED! THANKS FOR THE COMMENTS, CODE (it is in portuguese because I'm portuguese)

$usermaxuploads = mysql_query("SELECT MAX(uploads) as max_count FROM login");
$usermaxuploadsrow = mysql_fetch_array($usermaxuploads);
$maxvar = $usermaxuploadsrow["max_count"];

$usermaxresult = mysql_query("SELECT * from login WHERE uploads = '$maxvar' ");
$usermaxrow = mysql_fetch_array($usermaxresult);
echo $usermaxrow['usuario'];

Upvotes: 0

Views: 1531

Answers (3)

invisal
invisal

Reputation: 11171

I don't know if I understand the problem wrongly or just other answers were wrong. How is SELECT MAX(comments) as max_count, user FROM users returns the users with the most comment? It returns the number of the highest comment and the first user (which might not be the user with the most comment). Shouldn't the below query be the correct one?

SELECT user, comments FROM users ORDER BY comments DESC LIMIT 0, 1

Upvotes: 0

user2426701
user2426701

Reputation:

It should give you what you need:

SELECT MAX(comments) as tot, user FROM users;

Where tot will be the number of comments and user will be the relative user.

Upvotes: 1

Fabio
Fabio

Reputation: 23480

Yoy can use an ALIAS in the query to your MAX() function to be able to call it later with given name.

$usermaxresult = mysql_query("SELECT MAX(comments) as max_count, user FROM users");

Now you can print with

echo $usermaxrow['max_count'];
echo $usermaxrow['user'];

Upvotes: 0

Related Questions