Vivek Rai Vivek Rai - 3 months ago 15
MySQL Question

Multiple ordering on same column with different condition

I wand to order my column with different order on the behalf of other column condition.

There's a table that looks like this:

ID Date Status
--------------------------
1 - 16-JUL-2015 - 1
2 - 15-JUL-2015 - 1
3 - 11-JUL-2015 - 0
4 - 12-JUL-2015 - 0


if status is 1 then sort by Date ASC and is status is 0 sort by date DESC

Looking for following result

15-JUL-2015
16-JUL-2015
12-JUL-2015
11-JUL-2015


Any way to order this. Can't find anywhere

Thanks

Answer

You can use the following query:

SELECT *
FROM mytable
ORDER BY Status DESC,
         CASE 
            WHEN Status = 1 THEN DATEDIFF(Date, '1900-01-01')
            ELSE DATEDIFF('1900-01-01', Date) 
         END