title: PostgresSQL ORDER BY clause with CASE for sorted column
body: <p>I have a simple form of <code>SELECT ORDER BY</code> query which is using <code>CASE</code> for creating sorted id's. 
  I referred two approaches based on <a href="https://gist.github.com/cpjolicoeur/3590737" rel="nofollow noreferrer">https://gist.github.com/cpjolicoeur/3590737</a> and used one of them.</p>
  
  <pre><code>SELECT id FROM tasks t ORDER BY 
  CASE 
  WHEN t.type = 'TypeA' THEN 1 
  WHEN t.type = 'TypeB' THEN 2 
  WHEN t.type = 'TypeC' THEN 3 
  END, 
  CASE 
  WHEN t.state = 'Interrupted' THEN 4 
  WHEN t.state = 'Terminated' THEN 5 
  WHEN t.state = 'Completed' THEN 6
  WHEN t.state = 'Killed' THEN 7
  WHEN t.state = 'Warning' THEN 8
  WHEN t.state = 'Starting' THEN 9
  WHEN t.state = 'New' THEN 10
  WHEN t.state = 'Running' THEN 11
  END, 
  modified ASC;
  </code></pre>
  
  <p>The above query is based on below criteria to sort ids:</p>
  
  <ol>
  <li>TypeA with state:
  Interrupted,Terminated,Completed,Killed,Warning,Starting,New,Running</li>
  <li>TypeB with state:
  Interrupted,Terminated,Completed,Killed,Warning,Starting,New,Running</li>
  <li>TypeC with state:
  Interrupted,Terminated,Completed,Killed,Warning,Starting,New,Running</li>
  </ol>
  
  <p>I need to modify this sql for below sorting criteria now:</p>
  
  <ol>
  <li>TypeA with state: Interrupted,Terminated,Completed,Killed</li>
  <li>TypeB with state: Interrupted,Terminated,Completed,Killed</li>
  <li>TypeC with state: Interrupted,Terminated</li>
  <li>TypeA with state: Warning,Starting,New,Running</li>
  <li>TypeB with state: Warning,Starting,New,Running</li>
  <li>TypeC with state: Completed,Killed,Warning,Starting,New,Running</li>
  </ol>
  
  <p>Please note the ordering of 'state'(as specified) is equally important here for a 'type'</p>
  
  <p>I am trying to modify above sql so that it meets the new criteria. Being not an sql expert I am looking for most efficient way to rewrite this sql which could also accommodate slight changes on sorting criteria in near future.</p>
  
  <p>To further clarify sharing example below:</p>
  
  <p>sample data</p>
  
  <pre><code>id  | type  |    state
  ----+-------+-------------
   1  | TypeC | Completed
   2  | TypeA | Completed
   3  | TypeA | Running
   4  | TypeB | Completed
   5  | TypeB | Running
   6  | TypeC | Terminated
   7  | TypeC | Unknown
   8  | TypeA | Completed
   9  | TypeB | Interrupted
   10 | TypeB | Completed
   11 | TypeB | Interrupted
   12 | TypeC | Killed
   13 | TypeC | Running
   14 | TypeB | Warning
   15 | TypeB | Running
   16 | TypeB | Killed
  </code></pre>
  
  <p>expected data</p>
  
  <pre><code>id  | type  |    state
  --- +-------+-------------
   1  | TypeA | Completed
   8  | TypeA | Completed
   9  | TypeB | Interrupted
   11 | TypeB | Interrupted
   4  | TypeB | Completed
   10 | TypeB | Completed
   16 | TypeB | Killed
   7  | TypeC | Unknown
   6  | TypeC | Terminated
   3  | TypeA | Running
   14 | TypeB | Warning
   5  | TypeB | Running
   15 | TypeB | Running
   1  | TypeC | Completed
   12 | TypeC | Killed
   13 | TypeC | Running
  </code></pre>