MySQL order columns based on value

Question

I have multiple rows in a table and they have columns status and edited. Value of status can be 0, 1, 2 or 3. Following is my query.

SELECT * FROM orders ORDER BY FIELD(status, 1, 0, 2, 3), edited ASC

Is it possible to order results also based on the value of status like that:

if status = '0' => edited DESC
if status = '1' => edited DESC
if status = '2' => edited ASC
if status = '3' => edited ASC

Can I do this in one query or do I need to do separated queries?
Thanks in advance.

Not sure about the data but this might be helpful for you sqlfiddle.com/#!9/7e8bc/2 — Abhik Chakraborty
– Abhik Chakraborty, Commented Sep 3, 2015 at 13:06

Vishu238 · Accepted Answer · 2015-09-03 13:06:52Z

2

You should use Order By Case query

Syntax

 order By   
    CASE 
           WHEN status =0 THEN 0 
             ...

        END DESC

answered Sep 3, 2015 at 13:01

Vishu238

6735 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pradeep · Accepted Answer · 2015-09-03 13:31:22Z

0

Check this query too to get your sorted output:

SELECT * FROM test ORDER BY FIELD(status, 0, 1)DESC, FIELD(status, 2, 3)ASC, edited ASC

answered Sep 3, 2015 at 13:31

Pradeep

906 bronze badges