How to use ORDER BY inside UNION

Question

I want to use ORDER BY for every UNION ALL request, but I cannot figure out the correct syntax. This is what I want:

( SELECT id, user_id, other_id, name FROM tablename WHERE user_id = 123 AND user_in IN (...) ORDER BY name ) UNION ALL ( SELECT id, user_id, other_id, name FROM tablename WHERE user_id = 456 AND user_id NOT IN (...) ORDER BY name ) 

EDIT: Just to be clear: I need two ordered lists like this, and not one:

1 2 3 1 2 3 4 5

Thank you very much!

Answer 1

Something like this should work in MySQL:

 SELECT a.* FROM ( SELECT ... FROM ... ORDER BY ... ) a UNION ALL SELECT b.* FROM ( SELECT ... FROM ... ORDER BY ... ) b 

Note, however, that the outermost query is missing the ORDER BY (or GROUP BY ), the order in which rows are returned is NOT guaranteed.

If you need strings returned in a specific sequence, you must include ORDER BY in the outermost query. In many use cases, we can simply use ORDER BY for an external query to satisfy the results.

However, if you have a use case when you need all the rows from the first query returned before all rows from the second query, one parameter should include an additional discriminator column in each of the queries. For example, add ,'a' AS src to the first request ,'b' AS src to the second request.

Then the outermost query may include ORDER BY src, name , to ensure consistent results.

---

Followup

In the original query, ORDER BY in your queries is discarded by the optimizer; since ORDER BY is not used for an external query, MySQL can return rows in any order.

The “trick” in the query in my answer (above) depends on the behavior, which may be specific to some versions of MySQL.

Test case:

fill in tables

 CREATE TABLE foo2 (id INT PRIMARY KEY, role VARCHAR(20)) ENGINE=InnoDB; CREATE TABLE foo3 (id INT PRIMARY KEY, role VARCHAR(20)) ENGINE=InnoDB; INSERT INTO foo2 (id, role) VALUES (1,'sam'),(2,'frodo'),(3,'aragorn'),(4,'pippin'),(5,'gandalf'); INSERT INTO foo3 (id, role) VALUES (1,'gimli'),(2,'boromir'),(3,'elron'),(4,'merry'),(5,'legolas'); 

request

 SELECT a.* FROM ( SELECT s.id, s.role FROM foo2 s ORDER BY s.role ) a UNION ALL SELECT b.* FROM ( SELECT t.id, t.role FROM foo3 t ORDER BY t.role ) b 

the result is returned

  id role ------ --------- 3 aragorn 2 frodo 5 gandalf 4 pippin 1 sam 2 boromir 3 elron 1 gimli 5 legolas 4 merry 

Lines from foo2 returned "okay", followed by lines from foo3 , again "okay".

Please note (again) that this behavior is NOT guaranteed. (The behavior we observe is a side effect of the way MySQL handles inline views (views). This behavior may differ in versions after 5.5.)

If you need strings returned in a specific order, specify the ORDER BY for the outermost query. And this streamlining will apply to the entire set of results.

As I mentioned earlier, if I first need the rows from the first query and then the second query, I would include the discriminator column in each query and then include the discriminator column in the ORDER BY clause. I will also end the inline views and do something like that:

 SELECT s.id, s.role, 's' AS src FROM foo2 s UNION ALL SELECT t.id, t.role, 't' AS src FROM foo3 t ORDER BY src, role 

Answer 2

You just use one ORDER BY at the very end.

The union turns two choices into one logical choice. Order-order applies to the entire set, not to every part.

Do not use any partners. Just:

 SELECT 1 as Origin, blah blah FROM foo WHERE x UNION ALL SELECT 2 as Origin, blah blah FROM foo WHERE y ORDER BY Origin, z 

Answer 3

Do not use ORDER BY in a separate SELECT inside a UNION unless you use LIMIT with it.

MySQL docs in UNION explain why (focus):

To apply ORDER BY or LIMIT to a single SELECT, place the statement in parentheses that enclose SELECT:

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

However, using ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result because UNION creates an unordered set of rows by default. Therefore, using ORDER BY in this context is usually associated with LIMIT, so it is used to define a subset of the selected rows to retrieve for SELECT, although this does not necessarily affect the order of these rows in the final UNION result. If ORDER BY appears without a LIMIT in SELECT, it is optimized because it will have no effect anyway.

To use the ORDER BY or LIMIT clause to sort or limit the entire UNION result, copy the individual SELECT statements and place the ORDER BY or LIMIT after the last. The following example uses both articles:

 (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; 

It seems like an ORDER BY like the one below will give you what you want:

 ORDER BY user_id, name 

Answer 4

  (SELECT id, user_id, other_id, name FROM tablename WHERE user_id = 123 AND user_in IN (...)) UNION ALL (SELECT id, user_id, other_id, name FROM tablename WHERE user_id = 456 AND user_id NOT IN (...))) ORDER BY name 

You can also simplify this query:

 SELECT id, user_id, other_id, name FROM tablename WHERE (user_id = 123 AND user_in IN (...)) OR (user_id = 456 AND user_id NOT IN (...))