CODEWITHSUNDEEP
mysql
MrPeperonny
MrPeperonny

Reputation: 33

MySQL - ORDER BY, CASE and DESC

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?

Upvotes: 3

Views: 6825

Answers (3)

Riban
Riban

Reputation: 26

CASE only can return one expression, so you will have several cases:

CASE WHEN $var = 1 THEN myText1 END,
CASE WHEN $var = 2 THEN myText2 END,
CASE WHEN $var = 3 THEN someNumber END DESC

OR, optimized

CASE 
     WHEN $var = 1 THEN myText1,
     WHEN $var = 2 THEN myText2
END,
CASE WHEN $var = 3 THEN someNumber END DESC

Upvotes: 1

mustaccio
mustaccio

Reputation: 18945

Use math:

ORDER BY
CASE
    WHEN $var = 1 THEN myText1
    WHEN $var = 2 THEN myText2
    WHEN $var = 3 THEN 1000000 - someNumber
END

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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.

Upvotes: 8

Related Questions