MySQL - How to compare varchar decimals returned from CASE WHEN?

Question

I have a sql that I wish to sort by country first, then each country has its own sorting rules.

Here is my sql:

select country, state, creditLimit, salesRepEmployeeNumber, customerNumber from customers
order by field(country, 'UK', 'france', 'USA') desc,
(
case when country = 'USA' then state
    when country = 'UK' then salesRepEmployeeNumber
    when country  = 'france' then creditLimit
    else customerNumber
end
);

The problem is, creditLimit is of type decimal. But it looks like case when converted decimal to varchar, and creditLimit is compared as varchars:

118200.00
123900.00
 21000.00
 53800.00
 61100.00
 65000.00
 68100.00
 77900.00
 81100.00
 82900.00
 84300.00
 95000.00

I thought about casting the varchars back to decimal, but when it is not France, they should not be compared as decimals.

How to fix this? Also how could I sort France's decimalLimit in descending order?

Answer 1

The issue here is that you are trying to sort by a single CASE expression, but sometimes you want to use a text column, and sometimes you want to use a decimal column. You can't make state decimal, but you can make a decimal text. So, one approach here would be to convert the creditLimit decimal column to text and also left pad it with zeroes, so that it sorts properly as text:

SELECT country, state, creditLimit, salesRepEmployeeNumber,
       customerNumber
FROM customers
ORDER BY
    FIELD(country, 'UK', 'france', 'USA') DESC,
    CASE country WHEN 'USA'    THEN state
                 WHEN 'UK'     THEN salesRepEmployeeNumber
                 WHEN 'france' THEN LPAD(creditLimit, 13, '0')
                 ELSE customerNumber END;