title: SQL Server conditional Order By with multiple columns
body: <p>I have a table with thousands of executions that may have one of the following results in the state column:</p>
  
  <pre><code>OK
  NOTOK
  CANCELED
  RUNNING
  </code></pre>
  
  <p>I need a query where i can sort the results so that the executions running appear on top, and the rest of the results are sorted by start date or ID, according to user preference.</p>
  
  <p>example:
  Table records:</p>
  
  <pre><code>ID - STATE - START_DATE
  1      OK      12:00
  2    RUNNING   12:10
  3     NOTOK    12:30
  4    RUNNING   12:45
  5      OK      13:00
  </code></pre>
  
  <p>Expect result when sorted by START_DATE:</p>
  
  <pre><code>ID - STATE - START_DATE
  4   RUNNING    12:45
  2   RUNNING    12:10
  5     OK       13:00
  3    NOTOK     12:30
  1     OK       12:00
  </code></pre>
  
  <p>This is the query i already have (simplified):</p>
  
  <pre><code>SELECT TOP (@ITEMS_PAGE) ID, START_DATE, STATE
  FROM 
      (SELECT ROW_NUMBER() OVER (ORDER BY
                                    CASE WHEN @ORDER = 'ID&amp;ASC' THEN ID END ASC,
                                    CASE WHEN @ORDER = 'ID&amp;DESC' THEN ID  END DESC,
                                    CASE WHEN @ORDER = 'START_DATE&amp;ASC' THEN START_DATE END ASC,
                                    CASE WHEN @ORDER = 'START_DATE&amp;DESC' THEN START_DATE END DESC,
                                    CASE WHEN @ORDER = 'STATE&amp;ASC' THEN STATE END ASC,
                                    CASE WHEN @ORDER = 'STATE&amp;DESC' THEN STATE END DESC,
                                    CASE WHEN @ORDER IS NULL THEN ID END DESC
                                ) AS ROWNUMBERS, * 
       FROM @TMPPAGING 
       WHERE (@RESULT is null or STATE = @RESULT)
  ) S
  WHERE S.ROWNUMBERS &gt; (@CURRENT_PAGE - 1) * @ITEMS_PAGE
  </code></pre>
  
  <p>@TMPPAGING contains the records.</p>