CODEWITHSUNDEEP
mysqlsql-order-by
Matheus Hernandes
Matheus Hernandes

Reputation: 659

Using 'case' in ORDER BY (MySQL)

I'm working in a procedure that returns a huge select where I wan't define how it is order. I wan't choose one of three fields and if it will be ascending or descending, if none of the three options was define, it returns by default the first field in descending

this way

ORDER BY 
CASE option1 
    WHEN 0 THEN                 
        CASE option2 
            WHEN 0 THEN firstField DESC
            WHEN 1 THEN firstField ASC
        END 
    WHEN 1 THEN             
        CASE option2 
            WHEN 0 THEN secondField DESC
            WHEN 1 THEN secondField ASC
        END 
    WHEN 2 THEN             
        CASE option2 
            WHEN 0 THEN thirdField DESC
            WHEN 1 THEN thirdField ASC
        END                 
    ELSE 
        firstField DESC
    END 
END;

Of course, this didn't work... mysql acuses errors in words 'DESC' and 'ASC', how can I make this works??

Upvotes: 0

Views: 136

Answers (1)

Arth
Arth

Reputation: 13110

In order to do this, you have to move the DESC/ASC to outside the case as they come after the expression.

You should also separate the fields. In a CASE statement if the fields have different datatypes, they will be converted into one that fits all (usually VARCHAR) and this can mess up the ordering.

You could do something like:

ORDER BY CASE WHEN option1=0 AND option2=0 THEN firstField END DESC, 
         CASE WHEN option1=0 AND option2=1 THEN firstField END ASC, 
         CASE WHEN option1=1 AND option2=0 THEN secondField END DESC,
         CASE WHEN option1=1 AND option2=1 THEN secondField END ASC,
         CASE WHEN option1=2 AND option2=0 THEN thirdField END DESC,
         CASE WHEN option1=2 AND option2=1 THEN thirdField END ASC,
         firstField DESC

Each case will return NULL for all rows when not applicable and thus have the same value. In fact the first case is unnecessary as it will be caught by the default, but I have included it for clarity. This may result in a hugely expensive ordering process however!

I think I'd much rather dynamically build the SQL and I'd use an array of allowed ordering values to keep it secure.

Upvotes: 1

Related Questions