MYSQL - Using conditional order bys

Answer 1

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);

Answer 2

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

Answer 3

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
    );