[SOLVED] Most popular record in a table??

[simonp]

Hi,

 

I have a table with lots of entries, eg:

 

Bob

Fred

Bob

Graham

Bob

Harold

Bob

Frank

William

Fred

Bob

 

How can I find out which is the most popular name (ie which one appears the most often - in this example 'Bob')?

 

Thanks

 

Simon

[GingerRobot]

How about:

 

SELECT COUNT(*) AS `num`,`name` FROM `yourtable` GROUP BY(`name`) ORDER BY `num` DESC

 

If you do just want the most popular, rather than an ordered listing of popularity, stick a LIMIT clause on the end:

 

SELECT COUNT(*) AS `num`,`name` FROM `yourtable` GROUP BY(`name`) ORDER BY `num` DESC LIMIT 1

[simonp]

Hi -thanks for that.

 

I'm struggling to get the results - I'm using:

 

$sql2 = "SELECT COUNT(*) AS 'num','victim' FROM 'victims' GROUP BY('victim') ORDER BY 'num' DESC LIMIT 1";

@$result2=mysql_query($sql2,$db);

 

How do I get the most popular name now?!

 

Cheers

 

Simon

[GingerRobot]

You don't use single quotes for starters. You use single quotes to enclose strings within you query. The character i used was the backtick (`) - which you can use to surround table names, field names etc. You don't have to do this, but i think it looks cleaner and easier to read. Anyway, Try:

 

<?php
$sql2 = "SELECT COUNT(*) AS `num`,`victim` FROM `victims` GROUP BY(`victim`) ORDER BY `num` DESC LIMIT 1";
@$result2=mysql_query($sql2,$db);
if($result2){
$row = mysql_fetch_assoc($result2);
echo 'The most popular victim is: '.$row['victim'].' who has been the victim on '.$row['num'].' occassions';
}
?>

 

Not quite sure why being the victim makes you popular, but yeah!

[simonp]

GingerRobot - that's brilliant!

 

Works perfectly.

 

Thanks!

 

Simon

[GingerRobot]

No problem, glad to help.