For versions pre 8.0:
select distinct a.animal, (
select b.number
from animals b
where b.animal = a.animal
group by b.animal, b.number
order by count(*) desc
limit 1
) as number
from animals a;
Demo
With MySQL 8.0 (or MariaDB 10.2):
with count_all as (
select animal, number, count(*) as cnt
from animals
group by animal, number
), count_max as (
select animal, max(cnt) as cnt
from count_all
group by animal
)
select animal, number
from count_all
natural join count_max
Demo
Note: If there are ties - The first query will return only one row per animal. The second will return them all (tied rows). You didn't specify what to do in such case.
As pointed out by Juan Carlos Oropeza - In MySQL 8 you can also use window functions ROW_NUMBER()
or RANK()
with count_all as (
select animal, number, count(*) as cnt
from animals
group by animal, number
), count_max as (
select animal, number,
row_number() over (partition by animal order by cnt desc) as rn
from count_all
)
select animal, number
from count_max
where rn = 1
This will not return ties. If you want to get tied rows, just replace row_number()
with rank()
.