Returning the "most popular" row

I would like to return the most popular value of a particular field in a
mysql database:

For example:

forname, surname, county
------------------------
Alan, Smith, Wiltshire
John, Smith, Hampshire
Eric, Jones, Dorset

I would like to enter the db name (db) and fieldname (surname) and would
like the most popular surname returned (or at least a row(s) containing this
surname)

For example any of the following retirned would be superb

1. "Smith"
2. A row where the surname is "Smith" so I can extract "Smith" in PHP
3. All of the row(s) containing "Smith" so I can extract "Smith" in PHP

I'm using MySQL 4.0.20

Any comments?

Cheers

> I would like to enter the db name (db) and fieldname (surname) and would like the most po
pular surname returned (or at least a
> row(s) containing this surname)

If you include table name, then it could be something like:

SELECT fieldname, count(*) cnt
FROM table_name
GROUP BY fieldname
ORDER BY cnt DESC

First row of the result would be the one you look for (giving also
the count of requested value in specified table).
You can also limit the result to single row (LIMIT clause in MySQL,
TOP clause in MS SQL Server) like (I'm not sure, cause I do not
use MySQL):


SELECT fieldname, count(*) cnt
FROM db_name.table_name
GROUP BY fieldname
ORDER BY cnt DESC
LIMIT 1

Hilarion

One thing:
Two values may have the same top frequency, eg.:

SURNAME |  CNT
--------+------
Smith   |    7
Johnson |    7
Jones   |    5
Bond    |    1

You'll have to decide what to do in this case.

Hilarion

> SELECT fieldname, count(*) cnt
> FROM db_name.table_name
> GROUP BY fieldname
> ORDER BY cnt DESC
> LIMIT 1

Fantastic - perfect

Many thanks mate!

>
> SURNAME |  CNT
> --------+------
> Smith   |    7
> Johnson |    7
> Jones   |    5
> Bond    |    1
>
> You'll have to decide what to do in this case.

In this case it doesn't matter as it's just suggesting a category to put
something, it chooses the most popular category for each user's account.

Surnames was just an analogy

Cheers again