Question 113276 in Mysql Question posted in
The official MySQL documentation can be found here.

Retrieve the first and second best win results per age_group column per a region column in mysql table

MySql issue: I want to extract the two best age_groups per region based on their wins. I haven’t had much luck on this, having browsed similar issues. It’s probably straightforward but mysql isn’t playing nice for me this evening.

region age_group wins
london 35 52
paris 10 54
dublin 15 57
london 40 65
paris 20 68
dublin 35 73
paris 5 75
london 5 79
dublin 25 81
paris 15 81
london 30 82
dublin 20 83
london 20 85
london 10 87
london 25 87
paris 30 91
paris 25 91
dublin 40 94
dublin 30 96
dublin 5 96
london 15 99
dublin 10 100

Results should like something like this:

region best_age_category second_best_age_category
dublin 10 5
london 15 25
paris 25 30
Tags:

2

Answers


  1. 0

    Use ROW_NUMBER() OVER (<partition_definition> <order_definition>) to assign row numbers to your records and then filter where the row number is 1 or 2

    Login or Signup to reply.
  2. 0 
    select   region
        ,group_concat(case when dns_rnk = 1 then age_group end) as best_age_category
        ,group_concat(case when dns_rnk = 2 then age_group end) as second_best_age_category
from     (
         select *
                ,dense_rank() over(partition by region order by wins desc) as dns_rnk
         from   t
         ) t
group by region
    region best_age_category second_best_age_category
    dublin 10 5,30
    london 15 25,10
    paris 30,25 15
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search