0

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,

0

3 Answers 3

0
  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.

0

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.

2
  • tnx_order its boolean parameter which im passing to procedure. So in general if tnx_order is true, then i need to sort column called txn.id in ascending order. Else in descending
    – Denys TDV
    May 17 at 7:36
  • Then you want two sql statements with an if condition in your procedure to decide which one gets executed May 17 at 7:40
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.