Home > Archive > PHP SQL > November 2004 > Returning the "most popular" row
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Returning the "most popular" row
|
|
| Aaron Whiffin 2004-11-02, 8:56 am |
| 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
| |
| Hilarion 2004-11-02, 3:56 pm |
| > 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)
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
| |
| Hilarion 2004-11-02, 3:56 pm |
| 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
| |
| Aaron Whiffin 2004-11-02, 3:56 pm |
|
> SELECT fieldname, count(*) cnt
> FROM db_name.table_name
> GROUP BY fieldname
> ORDER BY cnt DESC
> LIMIT 1
Fantastic - perfect
Many thanks mate!
| |
| Aaron Whiffin 2004-11-02, 3:56 pm |
|
>
> 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
|
|
|
|
|