How to sort column value in mysql when results are grouped

Question

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

What version of MySQL are you using? If it's 8.x you can use `JSON_ARRAYAGG()` instead of `CONCAT()` and `GROUP_CONCAT()`. — Barmar, Jul 01 '22 at 16:23

score 0 · Answer 1 · answered Jul 01 '22 at 15:58

0

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;

answered Jul 01 '22 at 15:58

Henry

1,242
1
12
10

How to apply sorting in my query to get desired results which I mentioned above, thats the question. – Zahid Iqbal Jul 01 '22 at 16:06
the order by for you Zahid might read: ORDER BY name DESC; (where name in your example was marvel and DESC is Descending) – easleyfixed Jul 01 '22 at 16:16
But they want to sort by the Marvel revenue. This is not a single column, it's something derived from the join. – Barmar Jul 01 '22 at 16:21
You can als sort on the fields in the joined table. – Henry Jul 01 '22 at 18:57

score 0 · Answer 2 · answered Jul 01 '22 at 16:29

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

How to sort column value in mysql when results are grouped

2 Answers2