P.Yntema P.Yntema - 1 month ago 5
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

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".

Comments