|
Subject:
|
MySQL - Returning "most popular" row
|
|
Posted By:
|
whiffin
|
Post Date:
|
10/29/2004 9:52:08 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)
I'm using MySQL 4.0.20
All help appreciated
|
|
Reply By:
|
stu9820
|
Reply Date:
|
10/29/2004 10:26:08 AM
|
Do you mean the one with the most in the table? In your example, Smith.
Yippie Ki Yea, Mr. Falcon.
|
|
Reply By:
|
whiffin
|
Reply Date:
|
10/29/2004 10:39:49 AM
|
Yeah - I want one of the following
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
Cheers
|
|
Reply By:
|
whiffin
|
Reply Date:
|
11/2/2004 8:30:10 AM
|
Anyone?
|
|
Reply By:
|
whiffin
|
Reply Date:
|
11/2/2004 9:04:24 AM
|
SELECT surname, count(*) cnt
FROM db
GROUP BY surname
ORDER BY cnt DESC
LIMIT 1
Did the tricks - thanks
|
