get most common value

[jpratt]

Say i have a table with values like the following:

 

ID:      Name:

1        Bob

2        Bob

3        Bob

4        Nick

5        Nick

 

How would i get the value of Bob based off of the fact that it occurs most in the field? Im trying to write a query that selects the three values that occur most in a field. Any ideas? Thanks.

[jonsjava]

$sql = "SELECT `Name`, COUNT(*) FROM `table` GROUP BY `Name`;";

That should do the trick.

[spfoonnewb]

If you only want three values you may want the following:

 

SELECT COUNT(name) AS `total` , `name` FROM `table` GROUP BY `name` ORDER BY `total` DESC LIMIT 3