Select most common value from a field in MySQL

Question

I have a table with a million rows, how do i select the most common(the value which appears most in the table) value from a field?

Answer 1

You need to group by the interesting column and for each value, select the value itself and the number of rows in which it appears.

Then it's a matter of sorting (to put the most common value first) and limiting the results to only one row.

In query form:

SELECT column, COUNT(*) AS magnitude  FROM table  GROUP BY column  ORDER BY magnitude DESC LIMIT 1