MySQL UNION query from one table + ORDER BY

Question

I have one table with two queries and I need to sort it with descending type using ORDER BY. Here is my MySQL query that does not work properly:

(SELECT `text` 
FROM `comments` 
WHERE user_fr='".$user."' && archive='1'
ORDER BY `is_new_fr` DESC) 
    UNION
(SELECT `text` 
FROM `message` 
WHERE user_to='".$user."' && archive='1' 
ORDER BY `is_new_to` DESC)

Description!

is_new_fr and is_new_to counts total new messages.

Here is my table contant:

user_fr | user_to | archive | is_new_fr | is_new_to| text

name1 | name2 | 1 | 2 | 0 | testing...

name2 | name1 | 1 | 0 | 5 | testing ...

I want to make an order that 1st will display note that has more messages to few, or by another words using DESCending type.

This is the display on the page I want to do:

Open dialog with name2. Messages (5) Open dialog with name1. Messages (2)

Thank you!

Answer 1

The only way I know is a subquery:

SELECT `text`
FROM (
    SELECT `text`, `is_new_fr` AS `is_new`
    FROM `comments`
    WHERE user_fr = '".$user."'
    AND archive = '1'

    UNION

    SELECT `text`, `is_new_to` AS `is_new`
    FROM `message`
    WHERE user_to = '".$user."'
    AND archive = '1'
) ORDER BY `is_new` DESC