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 |
CodePudding user response：
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 |
CodePudding user response：
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