mysql collecting the name based on favorite and sorting the collected name with subsort-CodePudding

Prevously I asked a question here, but after re-read it, I realised it's more complicated so I'm asking a new question here.

I have following table: how to sort the table based on collecting the name from the same favorite, and also sorting the name based on their sum of score?

table: student
| FAVORITE | NAME   | score1 | score2 |  
|----------|--------|--------|--------|  
| math     | john   |     98 |     97 |  
| chem     | ryan   |     97 |    100 |  
| history  | yinyin |     80 |     95 |  
| math     | oda    |     99 |    100 |  
| history  | ed     |     67 |     85 |

expected result table after querying collecting name, and also sorting name:

table:favorite
| FAVORITE | NAME       |
|----------|------------|
| math     | john, oda  |
| chem     | ryan       |
| history  | ed, yinyin |

i've tried this:

SELECT FAVORITE, GROUP_CONCAT(
    select name from (
        select name, sum(score1   score2) as total_score
        from student
        group by name
        order by total_score asc
    ) as T
) AS NAME
FROM student
GROUP BY FAVORITE;

please help, thank you.

CodePudding user response：

You just need to provide the required value to order by using a derived table first, then specify the order in the group_concat syntax:

select FAVORITE, group_concat(NAME order by tot separator ', ') Name
from (
  select *, score1   score2 tot
  from t
)t
group by FAVORITE;

See Demo Fiddle

CodePudding user response：

You want to group the names and order them by the maximum amount of points scored.

SELECT FAVORITE, 
    GROUP_CONCAT(NAME) from my_table 
GROUP BY FAVORITE 
ORDER BY (MAX(score1   score2)) DESC;

Here is an SQL Fiddle, you can debug the query by passing scores to the group_concat function.

CodePudding user response：

Based on the previous answer, just add order by (score1 score2).

SELECT FAVORITE, GROUP_CONCAT(NAME order by (`score1` `score2`)) AS NAME
FROM yourTable
GROUP BY FAVORITE;