Question 195384 in Mysql Question posted in
The official MySQL documentation can be found here.

Using Subquery in ORDER BY clause in MySQL

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)

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    How I’d do it…

    SELECT 
    wc.ID_Project,
    wc.ID_Step,
    wc.Content
FROM
    ProjectBoard.Workstep_Content   AS wc
INNER JOIN
(
    SELECT 
        ID_Project,
        MIN(ID_Workstep) as orderme
    FROM
        ProjectBoard.Project_Assignment
    WHERE
        isDone = 0
    GROUP BY
        ID_Project
)
    AS pa
        ON pa.ID_Project = wc.ID_Project
ORDER BY
    pa.orderme,
    wc.ID_Step

    How others may do it…

    SELECT 
    wc.ID_Project,
    wc.ID_Step,
    wc.Content
FROM
    ProjectBoard.Workstep_Content   AS wc
INNER JOIN
    ProjectBoard.Project_Assignment AS pa
        ON pa.ID_Project = wc.ID_Project
WHERE
    pa.isDone = 0
GROUP BY
    wc.ID_Project,
    wc.ID_Step,
    wc.Content
ORDER BY
    MIN(pa.ID_Workstep),
    wc.ID_Step

    How anarchists may do it…

    SELECT 
    wc.ID_Project,
    wc.ID_Step,
    wc.Content
FROM
    ProjectBoard.Workstep_Content   AS wc
ORDER BY
    (
        SELECT
            MIN(ID_Workstep)
        FROM
            ProjectBoard.Project_Assignment
        WHERE
                ID_Project = wc.ID_Project
            AND isDone     = 0
    ),
    wc.ID_Step
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search