apply "ORDER BY" on a "UNION" (Mysql)
SELECT *
FROM (
(SELECT * FROM user_relation WHERE from_user_id = 1)
UNION
(SELECT * FROM user_relation WHERE to_user_id = 1)
) AS i
ORDER BY trust_degree
You have to assign an alias to your select. But in this case a UNION
is not necessary and could be replaced by a simple OR
, as @Karoly Horvath points out in his comment. The resulting query would look like this:
SELECT
*
FROM user_relation
WHERE from_user_id = 1 OR to_user_id = 1
ORDER BY trust_degree
It is written in the documentation of UNION
:
To apply
ORDER BY
orLIMIT
to an individualSELECT
, place the clause inside the parentheses that enclose theSELECT
:(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);
...
Use of
ORDER BY
for individualSELECT
statements implies nothing about the order in which the rows appear in the final result becauseUNION
by default produces an unordered set of rows....
To use an
ORDER BY
orLIMIT
clause to sort or limit the entireUNION
result, parenthesize the individualSELECT
statements and place theORDER BY
orLIMIT
after the last one. The following example uses both clauses:(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;
A statement without parentheses is equivalent to one parenthesized as just shown.
By applying the above information to your query it becomes:
(SELECT * FROM user_relation WHERE from_user_id = 1)
UNION
(SELECT * FROM user_relation WHERE to_user_id = 1)
ORDER BY trust_degree