[SOLVED] Concatenating students on basis of score in MySQL


Tabl scoretable:

id name score
101 L 10
101 M 9
101 N 10
102 O 10
102 X 10
103 P 8
104 Q 9
104 R 8


id first second third
101 L,N M
102 O,X
103 P
104 Q R

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

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;


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

Answered By – Salman A

Answer Checked By – Jay B. (BugsFixing Admin)

Leave a Reply

Your email address will not be published.