How to sort column value in mysql when results are grouped-CodePudding

I have three database tables projects which contains data about projects

Table "project":

project_id	name
10000	Project 1
20000	Project 2
30000	Project 3
40000	Project 4

Table "revenues":

project_id	revenue	fk_setting_id
10000	2000	10
10000	3300	20
20000	7000	10
30000	1000	10
30000	15000	20

Table "company":

setting_id	name
10	MARVEL
20	UNIVER

Now, I want to sort projects by column value [input = (sort_key = "MARVEL" order_by="DESC/ASC")] for example give me project sorted by "MARVEL"'s revenue DESC such that I get the results in order mentioned below:

col1	col2
20000	[{"name": "MARVEL", "revenue": "7000"}]
10000	[{"name": "MARVEL", "revenue": "2000"},{"name": "UNIVER", "revenue": "3300"}]
30000	[{"name": "MARVEL", "revenue": "1000"},{"name": "UNIVER", "revenue": "15000"}]
40000

I'm using this query but don't know how to perform sorting on such models to get desired above mentioned results:

SELECT p.project_id, p.name, stid.settings
FROM project p 
LEFT JOIN (SELECT sid.project_id, 
CONCAT('[', GROUP_CONCAT(
 JSON_OBJECT(
 'name', sas.name
,'revenue', sid.revenue
) SEPARATOR ',')
,']') AS settings
FROM revenues sid
JOIN company sas ON sas.fk_setting_id = sid.setting_id
GROUP BY sid.project_id) stid ON stid.project_id = p.project_id
LIMIT 0,20

CodePudding user response：

Sorting is done by "ORDER BY" is see none in your query.

SELECT column1, column 2…
FROM table_name
WHERE [condition]
GROUP BY column1, column2
ORDER BY column1, column2;

CodePudding user response：

Get the Marvel revenue in the subquery, so you can sort by it.

SELECT p.project_id, p.name, stid.settings
FROM project p 
LEFT JOIN (
    SELECT sid.project_id, 
        CONCAT('[', GROUP_CONCAT(
                JSON_OBJECT(
                    'name', sas.name
                    ,'revenue', sid.revenue
                ) SEPARATOR ',')
            ,']') AS settings,
        MAX(CASE WHEN sas.name = 'MARVEL' THEN sid.revenue END) AS marvel_revenue
    FROM revenues sid
    JOIN company sas ON sas.fk_setting_id = sid.setting_id
    GROUP BY sid.project_id
) stid ON stid.project_id = p.project_id
ORDER BY stid.marvel_revenue DESC
LIMIT 0,20