title: CASE WHEN statement for ORDER BY clause
body: <p>I am using SQL Server 2008 R2. </p>
  
  <p>I want the priority based sorting for records in a table. </p>
  
  <p>So that I am using CASE WHEN statement in ORDER BY clause. The ORDER BY clause is as below : </p>
  
  <pre><code>ORDER BY 
  CASE WHEN TblList.PinRequestCount &lt;&gt; 0 THEN TblList.PinRequestCount desc, TblList.LastName ASC, TblList.FirstName ASC, TblList.MiddleName ASC END, 
  CASE WHEN TblList.HighCallAlertCount &lt;&gt; 0 THEN TblList.HighCallAlertCount desc, TblList.LastName ASC, TblList.FirstName ASC, TblList.MiddleName ASC END,
  Case WHEN TblList.HighAlertCount &lt;&gt; 0 THEN TblList.HighAlertCount DESC, TblList.LastName ASC, TblList.FirstName ASC, TblList.MiddleName ASC END,
  CASE WHEN TblList.MediumCallAlertCount &lt;&gt; 0 THEN TblList.MediumCallAlertCount DESC, TblList.LastName ASC, TblList.FirstName ASC, TblList.MiddleName ASC END,
  Case WHEN TblList.MediumAlertCount &lt;&gt; 0 THEN TblList.MediumAlertCount DESC, TblList.LastName ASC, TblList.FirstName ASC, Patlist.MiddleName ASC END
  </code></pre>
  
  <p>But it gives <code>Incorrect syntax near the keyword 'desc'</code>  </p>
  
  <p>Any solution? </p>
  
  <p>Also I can have: </p>
  
  <pre><code>TblList.PinRequestCount &lt;&gt; 0 and TblList.HighCallAlertCount &lt;&gt; 0 and
  TblList.HighAlertCount &lt;&gt; 0` and TblList.MediumCallAlertCount &lt;&gt; 0 and  
  TblList.MediumAlertCount &lt;&gt; 0 
  </code></pre>
  
  <p>at the same time.</p>