Case condition in order clause in mysql stored procedure

Question

Example stored procedure:

DELIMITER //
CREATE PROCEDURE GetOfficeByCountry(IN yr_order tinyint(1))
 BEGIN
 SELECT * 
 FROM offices
 WHERE country LIKE 'A';
 ORDER BY
 CASE yr_order
        WHEN 1 THEN updated
        WHEN 2 THEN likes
        ELSE 1 END  
 ASC
 END //
DELIMITER ;

yr_order is a stored procedure parameter. The query is fine when the ASC is put after the condition:

  CASE yr_order
        WHEN 1 THEN updated 
        WHEN 2 THEN likes 
        ELSE 1 END  
  ASC

Can I put a different sort order for each condition like this:

  CASE yr_order
        WHEN 1 THEN updated ASC
        WHEN 2 THEN likes ASC
        WHEN 3 THEN updated DESC
        WHEN 4 THEN likes DESC
        ELSE 1 END

I'm getting a syntax error with that.Is there any way to use different sorting for each condition?

http://stackoverflow.com/questions/15621609/t-sql-conditional-order-by — Codeek, Dec 03 '14 at 11:34

score 1 · Accepted Answer · answered Dec 03 '14 at 11:31

No you can't, but you could do something like:

CASE yr_order
        WHEN 1 THEN updated 
        WHEN 2 THEN likes 
        WHEN 3 THEN -updated 
        WHEN 4 THEN -likes 
        ELSE 1 END  
ASC

assuming updated and likes are numeric. If they're not, you could do something like:

ORDER BY

   CASE yr_order
            WHEN 1 THEN updated 
            WHEN 2 THEN likes 
            ELSE 1 END  
    ASC,
   CASE yr_order

            WHEN 3 THEN updated 
            WHEN 4 THEN likes 
            ELSE 1 END  
    DESC

Case condition in order clause in mysql stored procedure

1 Answers1