title: Sorting after union all or order by case when
body: <p>To test these queries I’m running them in the SQL query wizard in Microsoft Access. I’m trying to modify an existing php file in order to display the high priority status alerts (O and P) at the top which are then ordered by opslogid in a descending order. After there are no more O and P status alerts I want to display the rest of the status alerts (A, I, R), ordered by opslogid in a descending order. This is the sample data and required output:</p>
  
  <p><strong>tblOpslog</strong></p>
  
  <pre><code>opslogid = 9999, 9998, etc.
  
  status = R, O, I, A, P
  
  opslogid                               status
  
  9999                                       P
  
  9996                                       P
  
  9994                                       O
  
  9991                                       O
  
  9998                                       I
  
  9997                                       I
  
  9980                                       A
  
  9979                                       A
  
  9978                                       A
  
  9930                                       R
  
  9928                                       R
  </code></pre>
  
  <p>My colleague suggested that i used the union all command, which resulted in me coming up with this:</p>
  
  <p><strong>NOTE:</strong> THE “<code>WHERE DELETED AND NO VIEW MUST REMAIN IN QUERY, IT IS VITAL IN THE OUTPUT OF THE DATABASE</code>”. ALSO I CANNOT MAKE ANY CHANGES TO THE DATABASE ITSELF.</p>
  
  <pre><code>select * from (SELECT * FROM tblOpslog
  Where Deleted = No AND Noview = No AND status in ('O','P'))
  union all
  select * from (Select * FROM tblOpslog Where Deleted = No AND Noview = No AND status in ('I','R', 'A'))
  Order by status, opslogid DESC;
  </code></pre>
  
  <p>The output of this is:</p>
  
  <pre><code>opslogid                               status
  
  9980                                       A
  
  9979                                       A
  
  9978                                       A
  
  9998                                       I
  
  9997                                       I
  
  9994                                       O
  
  9991                                       O
  
  9999                                       P
  
  9996                                       P
  
  9930                                       R
  
  9928                                       R
  </code></pre>
  
  <p>I have also tried this code:</p>
  
  <pre><code>SELECT *
  FROM tblOpsLog
  WHERE Deleted = No AND Noview = No AND status IN ('A', 'I', 'R', 'O', 'P')
  ORDER BY CASE WHEN status IN ('O', 'P') then 1
                ELSE 2
           END, opslogid DESC
  </code></pre>
  
  <p>Which has given me this error:</p>
  
  <pre><code>Syntax error (missing oeprator) in query expression 'CASE WHEN status IN ('O','P') then 1 ELSE 2 End'.
  </code></pre>
  
  <p>I would really appreciate it if someone can someone suggest what to fix/add to the code to display the desired output? Thank you</p>