Combined ORDER BY of two SELECT queries using UNION-CodePudding

How can I get combined order by results retrieved from these two queries joined by a UNION?

SELECT u.id, u.name, u.gender, n.user, n.other_user, n.type, n.notification, n.membership, n.link, n.created_at, p.photo FROM notifications n
INNER JOIN users u ON
CASE
  WHEN n.user = :me THEN u.id = n.other_user
  WHEN n.other_user = :me THEN u.id = n.user
END
LEFT JOIN photos p ON
CASE
  WHEN n.user = :me THEN p.user = n.other_user AND p.order_index = (SELECT MIN(order_index) FROM photos WHERE user = n.other_user)
  WHEN n.other_user = :me THEN p.user = n.user AND p.order_index = (SELECT MIN(order_index) FROM photos WHERE user = n.user)
END
UNION
SELECT '', '', '', '', '', '', n.notification, n.membership, n.link, n.created_at, '' FROM notifications n WHERE type = 'admin'

I want the returned records to be sorted in descending order as per their ids. For example, if the records returned from first query are 3,5,4,6,7 and from second query are 2,1,9 then all the records should be combined sorted like this 9,7,6,5,4,3,2,1.

I have tried this:

SELECT * FROM
(

   *THE WHOLE QUERY ABOVE*

) AS x 
ORDER BY x.id

This is not returning correct results. It is sorting the results from first query in descending order 7,6,5,4,3 and results from the 2nd query in ascending order 1,2,9. They are getting sorted individually instead of getting sorted together. How can I get them sorted combined together for 9,7,6,5,4,3,2,1.

CodePudding user response：

You can add the following statement to the end of your query (not on every union-ed query). Replace the number(s) with the corresponding place in the SELECT clause.

ORDER BY 1 ASC, 2 DESC

ASC - Ascending (default), DESC - Descending

CodePudding user response：

You have already found the issue yourself; you confused user ID and notification ID. So select the two, use alias names that tell which is which and sort:

select u.id as user_id, ..., n.id as notification_id, ...
from ...
union all
select ... from ...
order by notification_id;