CODEWITHSUNDEEP
mysql
javaGirl243
javaGirl243

Reputation: 99

MySQL: How to use CASE for ORDER BY clause

I have a stored procedure that must return a table after filtering rows based on inputs. 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 have tried the following queries but in vain. The queries below have been simplified to only contain the relevant clauses. The other filters work correctly with no 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 concatenated the 2 inputs to 1 in the format _ and tried this:

    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 always get error #1064. It is different in each of the above cases but always points to the 'CASE' part. This is the error for option number 2 mentioned above

##1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHEN 'col1' THEN col1_name END CASE 'asc' WHEN 'desc' THEN DESC ELSE ' at line 4

The problem doesn't seem to be the column name. It is the sort direction that isn't working. If I try each of the above options without the 'ASC' and 'DESC' parts, there is no problem.

Am I doing anything wrong here? Is there a better way to go about this apart from CASE?

MySQL version: 5.6

Upvotes: 3

Views: 5417

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

The best approach 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 verbose. But, remember that CASE is an expression that returns a single value. Hence you cannot include ASC and DESC as part of the THEN.

Also important is the issue of data types. The SQL compiler decides on a single type for CASE expression. This can cause unexpected issues when the columns have different types.

The simplest solution is just to use multiple CASE expressions.

Upvotes: 7

Related Questions