mysql union changing order-CodePudding

I'm having issues using the union clause. Individually, the two queries run the way that I want them to.

The first query should be students with grades 8 or higher. The data should be ordered by the grade DESC and if two students have the same grade, then it should be sorted by their name alphabetically.
The second query should be students with grade 7 or lower, the names should be null and their marks should be in ascending order

It's only when I put them together that the first query no longer has any order, the second query is fine. I made the second query, a nested query, to try to help but it didn't

(SELECT s1.name
        , g1.grade
        , s1.marks
FROM students s1
JOIN grades g1 ON s1.MARKS > g1.min_mark 
                 AND s1.MARKS < g1.max_mark
WHERE g1.grade > 7
ORDER BY grade DESC, name ASC)
UNION
(SELECT   s3.name
        , g2.grade
        , s2.marks
FROM students s2
LEFT JOIN grades g2 ON s2.MARKS > g2.min_mark 
                AND s2.MARKS < g2.max_mark
LEFT JOIN (SELECT * FROM students WHERE marks > 69) s3 ON s2.id = s3.id
WHERE g2.grade < 7
ORDER BY s2.marks)

CodePudding user response：

Use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result because UNION by default produces an unordered set of rows.1

It then gives this example of how to order the union result:

(SELECT a FROM t1 WHERE a=10 AND B=1)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2)
ORDER BY a LIMIT 10;

In your query you probably want to remove the order by in the individual sub-queries and add this after:

ORDER BY grade DESC, name, marks