# [SOLVED] Concatenating students on basis of score in MySQL

## Issue

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

Output:

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:

``````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;
``````

## Solution

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
``````