how to sort mysql two different values?

I have 2 Tables and i join them.

I have a special_id field, if the special_id field is > -1 then they should be printed out before the ones with special_id < -1. And then i want to order them by name.

How can i do that?

A simple order by special_id asc,开发者_开发知识库 name desc isnt enough.

try like this :

SELECT IF(special_id > -1 , 1 , 0 ) AS order_help  , m.*  FROM mytable m    
ORDER BY order_help DESC, name DESC

its the other way around:

ORDER BY special_id DESC, name ASC

You have to split your query in 2 (one for the case of special_id > -1, the other for special_id < -1, both sorted by username), add a constant to each of them, UNION them, then sort by the constant added earlier

Example:

(SELECT 1 AS main_order, t.* FROM table t WHERE special_id > -1 )
UNION
(SELECT 2 AS main_order, t.* FROM table t WHERE special_id < -1 )
ORDER BY main_order, name DESC

Try something like this :

(SELECT CASE WHEN special_id > -1 THEN 1 ELSE 0 END orderValue,t.* FROM table1 t)
 UNION
(SELECT CASE WHEN special_id > -1 THEN 1 ELSE 0 END orderValue,t.* FROM table2 t)
ORDER BY orderValue,name DESC;

Here are my results :

/*************************/
  | orderValue | name   |
  |     0      | foo    |
  |     0      | bar    |
  |     1      | foobar |
  |     1      | barfoo |
/************************/

Hope that helps.