How to display the most repeated value in a table when grouping?

Question

SQL

Good day!
How do I deduce from this table, when grouped by age, the most recurring hobby for each age group. Here, for example, there should be 18 - fishing, 20 - football.

Thanks in advance for your replies.

Answer 1

A bit tricky but works:

select *
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