title: MySQL: How to use CASE for ORDER BY clause
body: <p>I have a stored procedure that must return a table after filtering rows based on inputs. Two of the inputs are <code>sort_column</code> and <code>sort_dir</code>. The query must ORDER BY <code>sort_column</code> in the <code>sort_dir</code> direction(ASC or DESC).</p>
  
  <p>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.</p>
  
  <ol>
  <li><code>SELECT * FROM table ORDER BY sort_column sort_dir</code></li>
  <li><p><code>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</code></p></li>
  <li><p>I concatenated the 2 inputs to 1 in the format _ and tried this:</p>
  
  <pre><code>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
  </code></pre></li>
  </ol>
  
  <p>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</p>
  
  <blockquote>
    <p><strong>##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</strong></p>
  </blockquote>
  
  <p>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.</p>
  
  <p>Am I doing anything wrong here?
  Is there a better way to go about this apart from CASE?</p>
  
  <p>MySQL version: 5.6</p>