Ordering by multiple columns that match variables

Question

I currently have this mysql statement:

SELECT * FROM tablename
 WHERE column1 = 'yes'
 ORDER BY
       CASE column2 WHEN 'premium' THEN 1
                    WHEN 'basic' THEN 2
                    ELSE 999
       END,
       customer_id ASC

I'd like to add another column to the mix....so here is what I would ultimately like to do.

ORDER BY:
column2 = premium
THEN
column2 = basic
THEN
column3 = specialcustomer
THEN
display remaining results according to customer_id ASC

So the output, in the order I would like it to appear.

John Doe - premium, Sally Jones - premium, Jim Smith - basic - specialcustomer, Don Johnson - basic - notspecialcustomer, Mary Lee - basic - notspecialcustomer

Answer 1

SELECT * FROM tablename
 WHERE column1 = 'yes'
 ORDER BY
       CASE column2 WHEN 'premium' THEN 1
                    WHEN 'basic' THEN 2
                    ELSE 999
       END,
       IF(column3 = 'specialcustomer', 1, 2),
       customer_id ASC

column3 = 'specialcustomer' is the check that should be returning true if specialcustomer.

What is going on in the order by section is that you can picture this like 3 additional virtual columns which get their respective value from these expressions: 1) case ... 2) if ... 3) customer_id

And then rows of the data are sorted by these column values in order.