P.Yntema P.Yntema - 1 year ago 66
MySQL Question

Select value where most values exist of SQL

I have a user table where each record has a label. I want to select the label which occurs most in the user table. For example:

  1. Username Red

  2. Username Red

  3. Username Blue

  4. Username Blue

  5. Username Blue

The result must be an array containing: Blue, Red, as the label blue occurs more in the table than red. How can I obtain this result?

Answer Source

You can use group by and limit:

select label
from t
group by username
order by count(*) desc
limit 1;

This value has a name in statistics. It is called the "mode".

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download