Add country name for each medals columns for this particular Query. maximum medals each medal group with country name?

Question

 with t1 as
(select distinct oh.games,oh.noc,region as countrys from olympics_history oh inner join olympics_history_noc_regions hnr
on hnr.noc = oh.noc order by games),
t2 as
(select games,noc,count(medal) as gold_medals from olympics_history
where medal like '%Gold%'
group by noc,games
order by games),
t3 as
(select games,noc,count(medal) as Silver_medals from olympics_history
where medal like '%Silver%'
group by noc,games
order by games),
t4 as
(select games,noc,count(medal) as Bronze_medals from olympics_history
where medal like '%Bronze%'
group by noc,games
order by games),
t5 as
(select t1.games,countrys,gold_medals,Silver_medals,Bronze_medals
from t1 inner join t2 on (t1.noc = t2.noc and t1.games = t2.games)
inner join t3 on (t2.noc = t3.noc and  t2.games = t3.games)
inner join t4 on (t4.noc = t3.noc and  t4.games = t3.games)
order by games,countrys)
select games,max(gold_medals)as max_gold,max(silver_medals) as max_gold,max(bronze_medals) as max_bronze from t5
group by games
order by games

before last query i got out like this enter image description here My output enter image description here Actual output needed enter image description here

Im using Oracle database my questing is - take a example in max_gold column have value of 25. that 25 gold value belong to germany. so i need output like germany-25 in max_gold column. that values group by games (ex - 1896 Summer,1900 Summer,1904 Summer). in second column You have 18.

Answer 1

There's no sample data so I made up my own.

I guess you don't need that many CTEs; one should do (temp in my example) as it fetches all medals "at once", while the rank analytic function ranks them (so rnk = 1 represents the top countries.

Why rank and not row_number? Because of ties - what if two (or more) countries have the same number of medals? That's also why final query utilizes listagg aggregate (and not e.g. min or max).

OK, here we go.

Sample data:

SQL> with
  2  olympics_history (games, medal, region) as
  3    (select 1896, 'Gold'  , 'Austria' from dual union all
  4     select 1896, 'Gold'  , 'Austria' from dual union all
  5     select 1896, 'Gold'  , 'Belgium' from dual union all
  6     select 1896, 'Silver', 'Germany' from dual union all
  7     select 1896, 'Bronze', 'Austria' from dual union all
  8     select 1896, 'Bronze', 'Austria' from dual union all
  9     select 1896, 'Bronze', 'Canada'  from dual union all
 10     --
 11     select 1900, 'Gold'  , 'Germany' from dual union all
 12     select 1900, 'Gold'  , 'UK'      from dual union all
 13     select 1900, 'Silver', 'France'  from dual union all
 14     select 1900, 'Silver', 'France'  from dual union all
 15     select 1900, 'Silver', 'France'  from dual union all
 16     select 1900, 'Silver', 'Greece'  from dual
 17    ),

Query begins here:

 18  temp as
 19    (select games, medal, region, count(*) cnt,
 20       rank() over (partition by games, medal order by count(*) desc) rnk
 21     from olympics_history
 22     group by games, medal, region
 23    )
 24  select games,
 25    listagg(case when medal = 'Gold'   then region || ' - ' || cnt end, ', ') within group (order by region) as gold,
 26    listagg(case when medal = 'Silver' then region || ' - ' || cnt end, ', ') within group (order by region) as silver,
 27    listagg(case when medal = 'Bronze' then region || ' - ' || cnt end, ', ') within group (order by region) as bronze
 28  from temp
 29  where rnk = 1
 30  group by games;

     GAMES GOLD                 SILVER               BRONZE
---------- -------------------- -------------------- --------------------
      1896 Austria - 2          Germany - 1          Austria - 2
      1900 Germany - 1, UK - 1  France - 3

SQL>