MySQL: How to use CASE with ORDER BY clause

Question

I have a stored procedure that has to return a table after filtering rows based on input. Two of the inputs are sort_column and sort_dir. The query must ORDER BY sort_column in the sort_dir direction (ASC or DESC).

I tried the following query without success. The query below has been simplified to include only relevant terms. Other filters work fine without any issues.

1. SELECT * FROM table ORDER BY sort_column sort_dir

2. SELECT * FROM table ORDER BY CASE sort_column when 'col1' then col1_name WHEN 'col2' THEN col2_name END CASE sort_dir WHEN 'asc' THEN ASC ELSE DESC END

3. I connected 2 inputs to 1 input in _ format and tried the following:

   SELECT * FROM table ORDER BY CASE sort_input
     WHEN 'col1_asc' THEN col1_name ASC
     WHEN 'col1_desc' THEN col1_name DESC
     WHEN 'col2_asc' THEN col2_name ASC
     WHEN 'col2_desc' THEN col2_name DESC END

I keep getting error #1064. It's different in each of the above cases, but always points to the "CASE" section. This is a bug with option 2 mentioned above

##1064 - There is an error in your SQL syntax; check the manual for your MySQL server version for 'WHEN 'col1' THEN col1_name END CASE 'asc' WHEN 'desc on line 4 Correct syntax to use around ' THEN DESC ELSE '

The problem doesn't seem to be the column names. This is a sorting direction that doesn't work. If I try each of the above options without the "ASC" and "DESC" parts, there is no problem.

Am I doing something wrong here? Is there a better way besides CASE?

MySQL version: 5.6

Answer 1

The best way is multiple cases:

ORDER BY (CASE WHEN sort_input = 'col1_asc' THEN col1_name END) ASC,
         (CASE WHEN sort_input = 'col1_desc' THEN col1_name END) DESC,
         (CASE WHEN sort_input = 'col2_asc' THEN col2_name END) ASC,
         (CASE WHEN sort_input = 'col2_desc' THEN col2_name END) DESC,

This may seem lengthy. However, remember that CASE is an expression that returns a single value. Therefore, you cannot use ASC and DESC as part of THEN.

Equally important is the issue of data type. The SQL compiler determines the single type of the CASE expression. This can cause unexpected problems when columns are of different types.

The simplest solution is to use multiple CASE expressions.