CODEWITHSUNDEEP
mysqlcasesql-order-by
Edin Osmic
Edin Osmic

Reputation: 446

MYSQL ORDER BY CASE WHEN not working properly

MB_KOM are first locations returned from this query, then MLL,MDL...etc.

SELECT *, SUBSTRING(location, 7, 20) AS ExtractString FROM inventory
ORDER BY CASE
        WHEN location LIKE "MB_BN%" THEN location END ASC,
        CASE
        WHEN location LIKE "MB_B%" THEN location END ASC,
        CASE
        WHEN location LIKE "MB_KOM%" THEN ExtractString+0 END DESC

So what am i missing here?

Edit:
Goal is to get locations in this order:
MB_BN% ASC,
MB_B% ASC,
MB_KOM% DESC,
THEN anything else...

After MB_BN and MB_B and MB_KOM are always some numbers.
Example: MB_KOM199, MB_BN010406, MB_B12

Upvotes: 0

Views: 77

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562260

You are using ORDER BY with three expressions, so it sorts by the first expression alone, unless that results in a tie. Then it resolves the tie by the second expression. And so on.

Did you mean to use a single CASE expression with multiple cases?

ORDER BY CASE
    WHEN location LIKE 'MB_BN%' THEN location
    WHEN location LIKE 'MB_B%' THEN location
    WHEN location LIKE 'MB_KOM%' THEN ExtractString+0 END

But that has a few problems:

  • You can't mix ASC and DESC sort order in the same ORDER BY argument.
  • You can't mix sorting alphabetically and numerically in the same ORDER BY argument. Even though you used the +0 trick to cast the ExtractString to an numeric, it will be cast back to string to be compatible with the expression.
  • The CASE has no ELSE, so if the location matches none of the patterns, the result of the expression is NULL, and the sorting will be in some arbitrary order.

Here's a different solution:

SELECT *,
  CASE WHEN location LIKE 'MB_BN%' THEN 1 
       WHEN location LIKE 'MB_B%' THEN 2 
       WHEN location LIKE 'MB_KOM%' THEN 3 
      ELSE 4 END AS location_type,
  REGEXP_SUBSTR(location, '[[:digit:]]+') AS digits
FROM inventory
ORDER BY location_type ASC,  
  (CASE location_type WHEN 3 THEN -digits ELSE digits END)+0 ASC;

Dbfiddle

Upvotes: 2

Related Questions