SQL - Return Max Column Value

Question

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'];

`SELECT * FROM users ORDER BY comments DESC LIMIT 1` ? Be aware that you are using [an **obsolete** database API](http://stackoverflow.com/q/12859942/19068) and should use [a modern replacement](http://php.net/manual/en/mysqlinfo.api.choosing.php). — DCoder, Jun 08 '13 at 09:39
change `where` to `having` http://stackoverflow.com/questions/2905292/where-vs-having — shevski, Jun 08 '13 at 09:39

score 1 · Answer 1 · 2013-06-08T09:54:24.447

1

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.

edited Jun 08 '13 at 09:54

answered Jun 08 '13 at 09:40

That would return all the users from the table and the value of MAX(comments). I want to print the user with the MAX(comments). – Luis Parker Jun 08 '13 at 09:52
See my edited answer. Just remove GROUP BY users and it will give you what you need. I tested it now – Jun 08 '13 at 09:54
What is this? It prints me a number: 33 – Luis Parker Jun 08 '13 at 09:58

Fabio · Accepted Answer · 2013-06-08T09:51:08.270

0

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'];

edited Jun 08 '13 at 09:51

answered Jun 08 '13 at 09:40

Fabio

23,183
12
55
64

My mistake, now it shows the max comments but not the user with the max comments.. – Luis Parker Jun 08 '13 at 09:47
This isn't an answer to the question? – bestprogrammerintheworld Jun 08 '13 at 09:48
No, I want to print the user with the max comments.. Please read the topic again! – Luis Parker Jun 08 '13 at 09:48
If I print the max_count it will give me only the value of MAX(comments), not the user with the MAX(comments). – Luis Parker Jun 08 '13 at 09:54
I remember sometime ago I had a Reference #33 something like that – Luis Parker Jun 08 '13 at 09:56
@luis you are welcome dude. Don't forget to accept answer to help people in the future with similar problems. To accept you just need to click on the large green tick mark (✔) under the answer's score – Fabio Jun 08 '13 at 10:03

score 0 · Answer 3 · answered Jun 08 '13 at 10:32

0

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

answered Jun 08 '13 at 10:32

invisal

11,075
4
33
54

That was the started code, what I did was$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']; – Luis Parker Jun 09 '13 at 17:59

SQL - Return Max Column Value

3 Answers3