CODEWITHSUNDEEP
mysql
dikidera
dikidera

Reputation: 2043

Select most common value from a field in MySQL

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?

Upvotes: 41

Views: 58432

Answers (3)

Bojangles
Bojangles

Reputation: 101473

This thread should shed some light on your issue.

Basically, use COUNT() with a GROUP BY clause:

SELECT foo, COUNT(foo) AS fooCount 
FROM table
GROUP BY foo
ORDER BY fooCount DESC

And to get only the first result (most common), add

LIMIT 1

To the end of your query.

Upvotes: 28

Philip
Philip

Reputation: 157

In case you don't need to return the frequency of the most common value, you could use:

SELECT foo
FROM table
GROUP BY foo
ORDER BY COUNT(foo) DESC
LIMIT 1

This has the additional benefit of only returning one column and therefore working in subqueries.

Upvotes: 7

Jon
Jon

Reputation: 437336

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

Upvotes: 108

Related Questions