CODEWITHSUNDEEP
mysql
hagu81
hagu81

Reputation: 197

Get the most common value in mysql

I have a table with the fields animal and number like this:

    Horse   1
    Mouse   2
    Cat     2
    Horse   4
    Cat     2
    Mouse   1
    Horse   1
    Horse   3
    Mouse   2
    Cat     1

Now i want to get the most common value for each animal. So i expect this result:

Horse   1
Mouse   2
Cat     2

Is it possible to do this with one mySQL query? I have no idea how i could do that.

Upvotes: 1

Views: 120

Answers (2)

Paul Spiegel
Paul Spiegel

Reputation: 31832

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().

Upvotes: 2

PeterHe
PeterHe

Reputation: 2766

You can use a sub query to get that:

SELECT c.animal,MAX(c.cnt)
FROM (
  SELECT t.animal, t.number, count(*) AS cnt
  FROM your_table t
  GROUP BY t.animal, t.number) c
GROUP BY c.animal

Upvotes: 1

Related Questions