MySQL - ORDER BY, CASE and DESC

Question

I have a PHP variable called $var. I want to sort my result in dependence of $var. This one works:

$sql = "
SELECT
    myText1,
    myText2,
    someNumber
FROM myTable
ORDER BY
    CASE
        WHEN $var = 1 THEN myText1
        WHEN $var = 2 THEN myText2
        WHEN $var = 3 THEN someNumber
    END";

But when $var is 3 I need to sort by someNumber with DESC. So I tried this:

$sql = "
SELECT
    myText1,
    myText2,
    someNumber
FROM myTable
ORDER BY
    CASE
        WHEN $var = 1 THEN myText1
        WHEN $var = 2 THEN myText2
        WHEN $var = 3 THEN someNumber DESC
    END";

This one throws a syntax error. How can I achieve this?

Answer 1

Split this into three case expressions:

ORDER BY (CASE WHEN $var = 1 THEN myText1 END) ASC,
         (CASE WHEN $var = 2 THEN myText2 END) ASC,
         (CASE WHEN $var = 3 THEN someNumber END) DESC

The ASC is optional, of course -- there just to contract with the DESC.

More importantly, this removes the automatic conversion. A CASE expression returns only one type. When you mix different types, then some sort of conversion takes place. That conversion can affect the ordering of the values. This fixes that problem.