CODEWITHSUNDEEP
sqlmysql
Akshay Rawat
Akshay Rawat

Reputation: 4784

MYSQL - Using conditional order bys

I'm having problems using conditional ORDER BYs. I want to do something like this 

SELECT promotion_expires,created_at
FROM `notes` ORDER BY
CASE WHEN (promotion_expires > NOW()) THEN
    'promotion_expires DESC,created_at DESC'
ELSE
    'created_at DESC'
END;

ie. the result should first have rows where promotion_expires > NOW() ordered by 'promotion_expires, created_at' followed by other rows ordered by 'created_at'

For example - 


+---------------------+---------------------+
| promotion_expires   | created_at          |
+---------------------+---------------------+
| 2010-08-01 02:39:53 | 2010-07-24 02:39:54 |
| NULL                | 2010-07-23 02:39:54 |
| NULL                | 2010-07-25 02:39:54 |
| 2010-08-08 02:39:54 | 2010-07-27 02:39:54 |
| 2010-08-06 01:39:54 | 2010-07-27 01:39:54 |
| 2010-08-06 01:39:54 | 2010-07-27 02:39:54 |
+---------------------+---------------------+

Should be ordered like 


+---------------------+---------------------+
| promotion_expires   | created_at          |
+---------------------+---------------------+
| 2010-08-08 02:39:54 | 2010-07-27 02:39:54 |
| 2010-08-06 01:39:54 | 2010-07-27 02:39:54 |
| 2010-08-06 01:39:54 | 2010-07-27 01:39:54 |
| NULL                | 2010-07-25 02:39:54 |
| 2010-08-01 02:39:53 | 2010-07-24 02:39:54 |
| NULL                | 2010-07-23 02:39:54 |
+---------------------+---------------------+

I want to do this just using using conditional order bys and not unions

Thanks

Upvotes: 2

Views: 2152

Answers (3)

Haim Evgi
Haim Evgi

Reputation: 125466

need to add brackets.

the syntax like :

select ... from tablename order by
(case 
when "val1" then field1
when "val2" then field2
else field3 end)

see in the comment on this page

http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html

in your case

SELECT promotion_expires,created_at
FROM `notes` ORDER BY    
(CASE WHEN (promotion_expires > NOW()) THEN
        'promotion_expires DESC,created_at DESC'
    ELSE
        'created_at DESC'
    END);

Upvotes: 3

James Anderson
James Anderson

Reputation: 27478

Try: 

   SELECT promotion_expires, created_at FROM    
   ( SELECT promotion_expires,
           created_at ,
           CASE WHEN (promotion_expires > NOW()) 
                THEN promotion_expires 
           ELSE created_at END AS ORDER_DT1,
           CASE WHEN (promotion_expires > NOW()) 
                THEN created_at
           ELSE promotion_expires END AS ORDER_DT2

      FROM notes 
      ORDER BY ORDER_DT1 DESC, ORDER_DT2 DESC
    );

Upvotes: 0

Mchl
Mchl

Reputation: 62369

Try like this ORDER BY IF(promotion_expires > NOW(),promotion_expires,1) DESC, created_at DESC

Upvotes: 3

Related Questions