SQL Syntax Error when using Group by conditional expression



  • 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,



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


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2