Tabl scoretable:

Output:

Below is my solution where I am getting multiple rows for same id. My Solution:

with
t1 as(select ID, name, Score, dense_rank() over(partition by ID order by Score desc) as rnk from scoretable),
t2 as(select t1.id, (case when t1.rnk=1 then string_agg(t1.name,' ') end) as first  from t1 group by t1.id,t1.rnk),
t3 as(select t1.id, (case when t1.rnk=2 then string_agg(t1.name,' ') end) as second from t1 group by t1.id,t1.rnk),
t4 as(select t1.id, (case when t1.rnk=3 then string_agg(t1.name,' ') end) as third  from t1 group by t1.id,t1.rnk)

select distinct t1.id,t2.first,t3.second,t4.third
from t1,t2,t3,t4
where t1.id=t2.id and t2.id=t3.id and t3.id=t4.id 
group by t1.id,t2.first,t3.second,t4.third
order by t1.id;

CodePudding user response：

The ranking calculation is ok. But use conditional aggregation instead of three joins to build the results:

with cte as (
    select id
         , name
         , dense_rank() over (partition by id order by score desc) as dr
    from t
)
select id
     , group_concat(case when dr = 1 then name end separator ', ') as `first`
     , group_concat(case when dr = 2 then name end separator ', ') as `second`
     , group_concat(case when dr = 3 then name end separator ', ') as `third`
from cte
where dr <= 3
group by id