Ordering individual results in a UNION statement, or “Oh UNION/ORDER BY, how do I hate thee? Let me count the ways.”

Suppose we have the following columns in a table:

+-----+-----------------+
| id  | name            |
+-----+-----------------+
|  85 | Medicine        |
| 156 | Social Sciences |
| 166 | Other Fields    |
| 184 | Business        |
+-----+-----------------+
4 rows in set (0.00 sec)

Suppose as well that we want “Other Fields” to be the last column displayed. That is easy enough. We just need to write a UNION statement like so:

mysql> (SELECT id, name FROM table WHERE name != 'Other Fields') UNION (SELECT id, name FROM table WHERE name = 'Other Fields');
+-----+-----------------+
| id  | name            |
+-----+-----------------+
|  85 | Medicine        |
| 156 | Social Sciences |
| 184 | Business        |
| 166 | Other Fields    |
+-----+-----------------+
4 rows in set (0.00 sec)

Now suppose that you want the results to be alpha sorted otherwise. Meaning you want the results to be Business, Medicine, Social Sciences, Other Fields. That too is easy, you think to yourself. I’ll just add an ORDER BY clause inside the first SELECT statement and everything will be kosher. Well, let’s try your solution, mister smarty-pants.

mysql> (SELECT id, name FROM table WHERE name != 'Other Fields' ORDER BY name ASC) UNION (SELECT id, name FROM table WHERE name = 'Other Fields');
+-----+-----------------+
| id  | name            |
+-----+-----------------+
|  85 | Medicine        |
| 156 | Social Sciences |
| 184 | Business        |
| 166 | Other Fields    |
+-----+-----------------+
4 rows in set (0.00 sec)

Oh snap, that didn’t work. How about if you give it a global ORDER BY outside of all the parentheses? Well, that would be a GLOBAL order by like you just said. It would alpha sort everything. Meaning your ‘Other Fields’ won’t be at the very end anymore. So what is the solution? I’ll tell you. The solution is to use an independent sort column. Like this:

mysql> (SELECT id, name, 1 AS order_by FROM table WHERE name != 'Other Fields') UNION (SELECT id, name, 2 AS order_by FROM table WHERE name = 'Other Fields') ORDER BY order_by, name ASC;
+-----+-----------------+----------+
| id  | name            | order_by |
+-----+-----------------+----------+
| 184 | Business        |        1 |
|  85 | Medicine        |        1 |
| 156 | Social Sciences |        1 |
| 166 | Other Fields    |        2 |
+-----+-----------------+----------+
4 rows in set (0.00 sec)

Bam. After I struggled for a couple minutes to figure out this damn query, I found the UNION reference for MySQL 5.0 that shares this little tidbit:

To use an ORDER BY or LIMIT clause to sort or limit the entire UNION result, parenthesize the individual SELECT statements and place the ORDER BY or LIMIT after the last one.
[...]
To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT.
[...]
However, 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. Therefore, the use of ORDER BY in this context is typically in conjunction with LIMIT, so that it is used to determine the subset of the selected rows to retrieve for the SELECT, even though it does not necessarily affect the order of those rows in the final UNION result. If ORDER BY appears without LIMIT in a SELECT, it is optimized away because it will have no effect anyway.
[...]
To cause rows in a UNION result to consist of the sets of rows retrieved by each SELECT one after the other, select an additional column in each SELECT to use as a sort column and add an ORDER BY following the last SELECT. [...] To additionally maintain sort order within individual SELECT results, add a secondary column to the ORDER BY clause.