CODEWITHSUNDEEP
mysql
Muiter
Muiter

Reputation: 1520

CASE in ORDER BY

I am using this ORDER BY clause in my MySQL query:

"ORDER BY FIELD(best_id.status, 'open', 'bekeken', 'verstuurd', 'binnen'), best_id.datum_leveren ASC

Works fine.

Now I would like to make an difference with sorting the rows, ASC combined with DESC. I have been trying to achieve this by change the clause to:

ORDER BY FIELD(best_id.status, 'open', 'bekeken', 'verstuurd', 'binnen'),
CASE WHEN best_id.status = 'binnen' THEN  best_id.datum_leveren DESC, END
ELSE best_id.datum_leveren ASC END";

But I can't get it tow work. What is the right way for this or is it impossible to use an CASE in the ORDER BY clause?

Any help is much appreciated.

Upvotes: 0

Views: 92

Answers (1)

Martin Smith
Martin Smith

Reputation: 453047

You can use

ORDER  BY CASE
            WHEN best_id.status = 'binnen'
              THEN best_id.datum_leveren
            ELSE NULL
          END DESC,
          CASE
            WHEN best_id.status = 'binnen'
              THEN NULL
            ELSE best_id.datum_leveren
          END ASC

Upvotes: 3

Related Questions