mysql order by multiple conditions

Question

I have sql in below, that want to make multiple ORDER BY.

SELECT
    r.*,
    s.uzunluq,
    b.id,
    au.status_id as aksessuar_status,
    au.aksessuar_id,
    au.aksessuar
FROM
    seksiya s,
    result r
LEFT JOIN bosh_seksiya_aksessuar b
    ON
    b.bosh_seksiya = r.model AND
    b.ERK = :ses
LEFT JOIN aksessuar_up au
    ON au.model_id = r.res_id AND
    au.user_id = :user_id AND
    au.status_id = 9
WHERE 
    r.user_id = :user_id AND
    r.model=s.seksiya AND 
    s.erk = :ses AND
    r.status_id IN (1,2,3,4,5)
ORDER BY 
    r.res_id

I think to write php PDO is not important for you, guys, cause my question only about with this sql. This sql works very good, I just want to add extra function. So, look to this column: r.status_id IN (1,2,3,4,5)

I have given Order BY r.res_id

MY question: I want to use multiple ORDER for each status_id

HOW to order:

ORDER BY r.res_id DESC WHERE r.status_id IN (1,2)

AND

ORDER BY r.res_id WHERE r.status_id IN (3,4,5)

in this sql?

Answer 1

ORDER BY IF(r.status_id IN (1,2), r.res_id, NULL) DESC, r.res_id

A recordset sorted with this ORDER BY clause will first display all records with r.status_id IN (1,2) (since NULL values come last in a descending ordering), themselves sorted in descending order of r.res_id; followed by all other values sorted by r.res_id in ascending order.

Answer 2

you can use union syntax:

SELECT * FROM ((SELECT
    r.*,
    s.uzunluq,
    b.id,
    au.status_id as aksessuar_status,
    au.aksessuar_id,
    au.aksessuar,
    1 as query_order
FROM
    seksiya s,
    result r
LEFT JOIN bosh_seksiya_aksessuar b
    ON
    b.bosh_seksiya = r.model AND
    b.ERK = :ses
LEFT JOIN aksessuar_up au
    ON au.model_id = r.res_id AND
    au.user_id = :user_id AND
    au.status_id = 9
WHERE 
    r.user_id = :user_id AND
    r.model=s.seksiya AND 
    s.erk = :ses AND
    r.status_id IN (1,2)
ORDER BY 
    r.res_id DESC) as table1
UNION
(SELECT
    r.*,
    s.uzunluq,
    b.id,
    au.status_id as aksessuar_status,
    au.aksessuar_id,
    au.aksessuar,
    0 as query_order
FROM
    seksiya s,
    result r
LEFT JOIN bosh_seksiya_aksessuar b
    ON
    b.bosh_seksiya = r.model AND
    b.ERK = :ses
LEFT JOIN aksessuar_up au
    ON au.model_id = r.res_id AND
    au.user_id = :user_id AND
    au.status_id = 9
WHERE 
    r.user_id = :user_id AND
    r.model=s.seksiya AND 
    s.erk = :ses AND
    r.status_id IN (3,4,5)
ORDER BY 
    r.res_id) as table2) sa table3 ORDER BY query_order