Function for finding most common entries

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Function for finding most common entries

Post by Stryks »

Hi all,

Basically I am wanting to find the most common value entered in a field, but I cant seem to find what I am after. Have searched all over but it is very hard to find something when you dont know if it can even be done.

All I can seem to get going is an average, which returns the result of a calculation as opposed to an actual row value.

Thanks.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

SELECT *, COUNT(field) `duplicates` FROM table GROUP BY field ORDER BY COUNT(field)
or similar should do it.
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Post by Stryks »

Damn ... I had to write it to understand it.

I just get the count of each grouped value and sort by this count, returning the original value with it. Take the first vlue and there you go.

Cheers anyhow. :wink:
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Post by Stryks »

Damn you were fast. Beat me to my own reply.

:D
Post Reply