SQL Syntax Error when using Group by conditional expression

Question

I'm trying to run SQL procedure with group by conditional expression

ORDER BY 
    CASE
        (WHEN tnx_order = TRUE THEN `txn`.id ASC) END,
        (WHEN tnx_order = FALSE THEN `txn`.id DESC) END
    LIMIT 1;

And i get this type of error:

SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHEN tnx_order = TRUE THEN `txn`.id ASC) END,

Answer 1

1. Your parenthesis are wrong. The wrapped construction is not single scalar expression.
2. You cannot use ASC/DESC modifier as a subpart of the expression.

If txn.id is numeric then use simple

ORDER BY 
    CASE WHEN tnx_order 
         THEN txn.id 
         ELSE -txn.id 
         END 
    LIMIT 1;

If it is not numeric then use

ORDER BY 
    CASE WHEN tnx_order
         THEN txn.id 
         ELSE 0
         END,
    CASE WHEN tnx_order
         THEN 0 
         ELSE txn.id
         END DESC
    LIMIT 1;

PS. tnx_order is boolean, hence additional comparing it with TRUE or FALSE makes no sense. It can be used as-is.

Answer 2

Where is tnx_order coming from? You have additional parenthesise and you can’t return asc or desc within the case statement.

You might want

ORDER BY CASE WHEN tnx_order THEN txn.id ELSE NULL END ASC
, CASE WHEN !tnx_order THEN txn.id ElSE NULL END DESC
LIMIT 1;

If tnx_order is a variable then you’d probably be better off having two different queries with the alternate order by clauses and have your application decide which to run. This would better take advantage of indexes to provide no sort plans.

Answer 3

You are in a stored procedure? You have the tools there to "construct" the query. Use the proc's IF statement or function to help you decide what to CONCAT together to make the query.

SET @sql = CONCAT( "SELECT ... ORDER BY id ",
                   IF(tnx_order, 'ASC', 'DESC') );

Then PREPARE and EXECUTE @sql.

Easy, clear, and possibly generates a more efficient query.