{question}
Do subqueries in SQL statements preserve the ORDER BY clause?
{question}
{answer}
A short answer is No.
The below examples will show how SingleStore subqueries don’t preserve the ORDER BY clause, and also show how GROUP_CONCAT can be used to perform the ORDER BY within this function in a sub-query.
The first example shows that the select with GROUP_CONCAT function and ORDER BY clause works:
SELECT GROUP_CONCAT(`Report`) AS `Report` FROM (SELECT distinct `Report` FROM `SortingTest1` ORDER BY `Report`) x;
+---------+
| Report |
+---------+
| A,B,C,D |
+---------+
The second example has the same SQL within a sub-query and it doesn’t work:
SELECT * FROM SortingTest1 r
JOIN (SELECT GROUP_CONCAT(`Report`) AS `Report2` FROM
(SELECT distinct `Report` FROM `SortingTest1` ORDER BY `Report`) x
) x;
+--------+---------+
| Report | Report2 |
+--------+---------+
| A | C,A,B,D |
| B | C,A,B,D |
| C | C,A,B,D |
| D | C,A,B,D |
+--------+---------+
In the third example, we show that moving the ORDER BY from the subquery ORDER BY clause to the GROUP_CONCAT, makes everything work again:
SELECT * FROM SortingTest1 r
JOIN (SELECT GROUP_CONCAT(`Report` order by `Report`) AS `Report2` FROM
(SELECT distinct `Report` FROM `SortingTest1`) x
) x;
+--------+---------+
| Report | Report |
+--------+---------+
| A | A,B,C,D |
| B | A,B,C,D |
| C | A,B,C,D |
| D | A,B,C,D |
+--------+---------+
{answer}