user3255412 user3255412 - 4 months ago 11
MySQL Question

MySql : Retrieve most occurring value in column for another column

I'm looking for a way in MySql to select the most occurring value in one column. egx this table

+-------+-------+
| Name | Signin|
+-------+-------+
| Name1 | 1 |
+-------+-------+
| Name1 | 2 |
+-------+-------+
| Name1 | 1 |
+-------+-------+
| Name1 | 3 |
+-------+-------+
| Name1 | 2 |
+-------+-------+
| Name1 | 2 |
+-------+-------+
| Name2 | 4 |
+-------+-------+
| Name2 | 5 |
+-------+-------+
| Name2 | 5 |
+-------+-------+
| Name2 | 5 |
+-------+-------+
| Name2 | 5 |
+-------+-------+
| Name2 | 6 |
+-------+-------+


The colum Signin has two=1, three=2, one=3 sow for the Name1 the most recent value is 2, for the Name2 the most recent value is 5 as in the colum Signin are one=4, four=5, one=6

The result i need is like this

Name1 - 2
Name2 - 5

Answer

What you are looking for is called the "mode" in statistics. Most databases support with CTEs or window functions which simplify the calculation. If your data is not too big, I think this following aggregation trick is the easiest method:

select name,
       substring_index(group_concat(signin order by cnt desc), ',', 1) as signin
from (select name, signin, count(*) as cnt
      from t
      group by name, signin
     ) ns
group by name;

The above does not always work. One possibility is sgeddes's approach using two aggregations. Another is to use variables:

select ns.*
from (select ns.*,
             (@rn := if(@n = name, @rn + 1,
                        if(@n := name, 1, 1)
                       )
             ) as rn
      from (select name, signin, count(*) as cnt
            from t
            group by name, signin
            order by name, count(*) desc
           ) ns cross join
           (select @n := '', @rn := 0) params
     ) ns
where seqnum = 1;