select "order by occurrence" mysql

July 28, 2005 2:35 PM

Does any one know how to find the most common result for a column returned from a mysql select request?
posted by outofpaper to Computers & Internet (6 answers total)

Untested, but something like..

SELECT *, COUNT(desiredcolumn) AS occurances FROM table GROUP BY occurances ORDER BY occurances DESC LIMIT 1;

should work (may need a bit of fixing).
posted by wackybrit at 2:40 PM on July 28, 2005

Best answer: Correction already:

SELECT *, COUNT(desiredcolumn) AS occurances FROM table GROUP BY desiredcolumn ORDER BY occurances DESC LIMIT 1;
posted by wackybrit at 2:40 PM on July 28, 2005

Tested. It works. If you have trouble, let us know.
posted by wackybrit at 2:45 PM on July 28, 2005

Of course, if I were to be really pendantic to myself, I'd point out it's "occurrences".. duh :) I'll stop posting for a minute now...
posted by wackybrit at 2:46 PM on July 28, 2005

An if you want to tack on a limiting clause to an aggregated SQL query, you'll have to use "HAVING x" instead of "WHERE x" - took me a while to remember that last week ..

so it'd become
SELECT *, COUNT(desiredcolumn) AS occurances FROM table GROUP BY desiredcolumn HAVING occurances > 5 ORDER BY occurances DESC LIMIT 1
posted by oliyoung at 4:27 PM on July 28, 2005

But you don't need the HAVING occurrences > 5 unless you're sure that there is a case where there are more than 5 repeats, otherwise you'll exclude your result if the maxcount happens to be only 4. Also that clause induces additional calculation overhead. The LIMIT 1 will do what you originally asked.
posted by matildaben at 6:40 PM on July 28, 2005