MYSQL: How to SELECT the highest occurance from a column?

suttercain

Hi everyone,

Let's say I have a column with names in it. Let's say I have 1000 or so records. Now I want to be able to find the name that appears the most. Let's say John appears 900 times, Rich 20 times, and other 1 time.

How do I get the result to ONLY show John, since it occurred the most times in the column?

I know COUNT(name) will give me the amount of times they occur, but I need to limit it down to only the one name...

Maybe a PHP function to filter the array? If not, can I do this in the MySQL query only?

Thank in advance for your help and time.

bradgrafelman

SELECT name, col1, col2 FROM myTable GROUP BY name ORDER BY COUNT(*) DESC LIMIT 1

suttercain

Hi bradgrafelman,

I ran this code:

<?php
$count = mysql_query("SELECT comicId, ipAddress FROM comicCounter ORDER BY COUNT(*)") or die(mysql_error());
while ($row = mysql_fetch_array($count)) {
echo $row['comicId']. " IP:" .$row['ipAddress'];
echo "<br />";
}
?>

And this was printed to the browser:
Invalid use of group function

suttercain

I also tried:

$count = mysql_query("SELECT comicId, ipAddress FROM comicCounter GROUP BY comicId ORDER BY COUNT(*) DESC LIMIT 1")

Same error

bradgrafelman

Hrm... maybe this? :

SELECT comicId, ipAddress, COUNT(*) AS num
FROM comicCounter
GROUP BY comicId
ORDER BY num DESC
LIMIT 1

Sxooter

bradgrafelman wrote:

Hrm... maybe this? :

SELECT comicId, ipAddress, COUNT(*) AS num
FROM comicCounter
GROUP BY comicId
ORDER BY num DESC
LIMIT 1

More than likely he's just using a version of MySQL that has ansi SQL turned on by default. ANSI SQL says you can't select a column you didn't group by unless it's either unique or has an aggregate function run on it.

if you group by comicId, then ipAddress isn't unique, you need to use an aggregate function on it.

Try a proper query, like:

SELECT comicId, max(ipAddress) as ipaddress, COUNT(*) AS num
FROM comicCounter
GROUP BY comicId
ORDER BY num DESC
LIMIT 1