MySQL: can't order one side of a union

Question

I have a query like this:

(SELECT * FROM something WHERE ...) UNION (SELECT * FROM something WHERE ...)

Because I want the results from the first SELECT to come out on top. But, the second part comes out in reverse order. I can't simply order the entire query because it will push the first SELECT that I need at the top to the bottom... So I want to do something like this:

(SELECT * FROM something WHERE ...) UNION (SELECT * FROM something WHERE ...  ORDER BY timestamp DESC)

But this just doesn't work, the ORDER BY is completely ignored; I tested this by inserting gibberish, ORDER BY dosaif30h still works... One solution would be to make two separate queries, but I'd much prefer to handle it in one.

possible duplicate of [Using union and order by clause in mysql](http://stackoverflow.com/questions/3531251/using-union-and-order-by-clause-in-mysql) — Jaugar Chang, Jan 14 '15 at 02:26
@JaugarChang that question is looking to order the entire UNION result... not the same problem — Christopher Reid, Jan 14 '15 at 02:31
Do you get the same result using Union All? Union eliminates duplicates; the engine has to sort the results to do that efficiently. — Allan, Jan 14 '15 at 03:34

Shahar · Accepted Answer · 2015-01-14T02:34:44.520

3

SELECT * FROM something WHERE ... UNION SELECT * FROM (SELECT * FROM something WHERE ...  ORDER BY timestamp DESC)a

edited Jan 14 '15 at 02:34

answered Jan 14 '15 at 02:23

Shahar

1,687
2
12
18

without the a on the end I got `OperationalError: (1248, 'Every derived table must have its own alias')` and with it... `Syntax Error` – Christopher Reid Jan 14 '15 at 02:26
@AllTheTime Note the `a` I added at the end. That's the alias that you must have. Add that. – Shahar Jan 14 '15 at 02:26
Your answer is super close and it got me to this... which works. Edit your answer and I will accept. `(SELECT * FROM something WHERE ...) UNION ( SELECT * FROM (SELECT * FROM something WHERE ... ORDER BY timestamp DESC) as a )` – Christopher Reid Jan 14 '15 at 02:28
@AllTheTime Try doing what I did in my answer, that should work as well (that's how I do it). Actually, it's equivalent to what you have, except less characters (better readability?). – Shahar Jan 14 '15 at 02:30
strange. Must be the version / engine I'm using (MySQLdb for python) It only works for me with the brackets and verbosity displayed in the above comment. – Christopher Reid Jan 14 '15 at 02:33
@AllTheTime Oh I found the mistake. The first statement shouldn't have had the parentheses. Try it now. – Shahar Jan 14 '15 at 02:38
1

This answer is not correct. There is no guarantee that the results of a `union` will be in the order of the query or that the ordering in a subquery is respected. In most cases, the only way to guarantee the ordering of a query is to use an `order by` at the outermost level. – Gordon Linoff Jan 14 '15 at 03:28

score 1 · Answer 2 · answered Jan 14 '15 at 02:26

1

If you want to order both of them in one order, then you need something like this:

SELECT * FROM (
    SELECT * FROM table_1 where something
    UNION
    SELECT * FROM table_2 WHERE something
) as tt
ORDER BY something

Note that both tables should have same column names and count.

If you want to order only the second query then:

SELECT * FROM table_1 where something
        UNION
SELECT * FROM (SELECT * FROM table_2 WHERE something ORDER BY something) as tt

answered Jan 14 '15 at 02:26

bksi

1,606
1
23
45

1

I don't think you need the sub-query. `ORDER BY` applies to the entire union – Phil Jan 14 '15 at 02:26
I only want to order the second `select` before the union is made – Christopher Reid Jan 14 '15 at 02:27

score 1 · Answer 3 · answered Jan 14 '15 at 03:27

If you want the first table to come first, then you need to order by that explicitly:

SELECT s.*
FROM ((SELECT s.*, 1 as which FROM something s WHERE ...)
      UNION ALL
      (SELECT s.*, 2 as which FROM something s WHERE ...)
     ) s
ORDER BY which, timestamp DESC

You can only trust an order by in the outer query -- in most circumstances -- if you want the results in a particular order.

NOTE: You can write this without the subquery if you like:

SELECT s.*, 1 as which FROM something s WHERE ...
UNION ALL
SELECT s.*, 2 as which FROM something s WHERE ...
ORDER BY which, timestamp;

MySQL: can't order one side of a union

3 Answers3