MySQL UNION query from one table + ORDER BY
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!
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
精彩评论