Page 1 of 1

Function for finding most common entries

Posted: Mon Feb 14, 2005 11:40 pm
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.

Posted: Mon Feb 14, 2005 11:42 pm
by feyd

Code: Select all

SELECT *, COUNT(field) `duplicates` FROM table GROUP BY field ORDER BY COUNT(field)
or similar should do it.

Posted: Mon Feb 14, 2005 11:44 pm
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:

Posted: Mon Feb 14, 2005 11:45 pm
by Stryks
Damn you were fast. Beat me to my own reply.

:D