Using Subquery in ORDER BY clause in MySQL

June 27, 2023

I got a few tables i want to join together.

The first script is the following

SELECT 
    Project_Assignment.ID_Project,
     MIN(Project_Assignment.ID_Workstep) as orderme  // this field i removed for mysql field() function
FROM
    ProjectBoard.Project_Assignment
WHERE
    Project_Assignment.isDone = 0
GROUP BY Project_Assignment.ID_Project
ORDER BY MIN(Project_Assignment.ID_Workstep)

and it returns

| ID_Project | orderme |
| 12         | 1       |
| 8          | 2       |
| 11         | 4       |
| 16         | 9       |

My second query is like

SELECT 
    Workstep_Content.ID_Project,
    Workstep_Content.ID_Step,
    Workstep_Content.Content
FROM
    ProjectBoard.Workstep_Content

and returns

| ID_Project | ID_Step | Content    |
| 11         | 11      | one        |
| 11         | 12      |another one |
| 12         | 1       |  two       |
| 8          | 11      | this       |
| 16         | 1       | first      |
| 16         | 4       | second     |

Now I want to order the results of my second query by their order of ID_Project in the first query.
The result should look like:

| ID_Project | ID_Step | Content    |
| 12         | 1       |  two       |
| 8          | 11      | this       |
| 11         | 11      | one        |
| 11         | 12      |another one |
| 16         | 1       | first      |
| 16         | 4       | second     |

I tried something with the MySQL FIELD function in the ORDER BY clause but it dint worked (ORDER BY FIELD(ID_Project,first_query)

>Solution :

Join with the subquery, then use ORDER by orderme

SELECT 
    w.ID_Project,
    w.ID_Step,
    w.Content
FROM
    ProjectBoard.Workstep_Content AS w
JOIN (
    SELECT 
        Project_Assignment.ID_Project,
         MIN(Project_Assignment.ID_Workstep) as orderme  // this field i removed for mysql field() function
    FROM
        ProjectBoard.Project_Assignment
    WHERE
        Project_Assignment.isDone = 0
    GROUP BY Project_Assignment.ID_Project
) AS o ON w.ID_Project = o.ID_Project
ORDER BY o.orderme