Как вывести наиболее повторяющееся значение в таблице при группировке?

Question

[delongeroman]

Доброго времени суток!
Как мне вывести из этой таблице при группировке по возрасту самое повторяющееся хобби для каждой возрастной группы. Тут, например, должно быть 18 - fishing, 20 - football. Вывести нужно только 2 колонки(возраст и хобби), без подсчета количества.

Answer 1

[Rsa97]

Для MySQL 8

SELECT DISTINCT `age`, FIRST_VALUE(`hobbies`) OVER `win` AS `hobbies`
  FROM (
    SELECT `age`, `hobbies`, COUNT(*) AS `count`
      FROM `test`
      GROUP BY `age`, `hobbies`
  ) AS `t`
  WINDOW `win` AS (
    PARTITION BY `age`
    ORDER BY `count` DESC
  )

Answer 2

[Slava Rozhnev]

select 
	age_hobbies.age, 
	age_hobbies.hobbies
from (
  select age, hobbies, count(*) cnt
  from user_hobbies
  group by age, hobbies
) age_hobbies
join (
select age, max(cnt) maxcnt
from (
  select age, hobbies, count(*) cnt
  from user_hobbies
  group by age, hobbies
) h group by age
) max_age_hobbies on 
	age_hobbies.age = max_age_hobbies.age and 
	age_hobbies.cnt = max_age_hobbies.maxcnt;

Mariadb fiddle

Comments

[delongeroman]

Благодарю

[Slava Rozhnev]

Лентюй, согдасен. Буду признателен за более эффективный запрос

[delongeroman]

Slava Rozhnev, а есть возможность как-то упростить данную конструкцию?

[Slava Rozhnev]

Запрос можно упростить оформив часть кода как view

create view age_hobbies as 
  select age, hobbies, count(*) cnt
  from user_hobbies
  group by age, hobbies
;

select 
	age_hobbies.age, 
	age_hobbies.hobbies
from age_hobbies
join (
	select age, max(cnt) maxcnt from age_hobbies group by age
) max_age_hobbies on 
	age_hobbies.age = max_age_hobbies.age and 
	age_hobbies.cnt = max_age_hobbies.maxcnt;

Если используется MySQL 8.0 или MariaDB 10 можно использовать window functions:

create view age_hobbies as 
  select age, hobbies, count(*) cnt
  from user_hobbies
  group by age, hobbies
;

select 
	age,
	hobbies
from (
  select 
	  age,
	  hobbies,
	  row_number() over (partition by age order by cnt desc) rnm
  from age_hobbies
) age_hobbies where rnm = 1;

MariaDB fiddle