title: incrementing an ORDER BY statement
body: <p>I have a query that looks like this:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT ElapsedTime, COUNT(SurgeryID) as numberOfOps
  FROM Table
  WHERE Doctor = 'Dr. ABC'
  AND numberOfOps &gt; 5
  GROUP BY ElapsedTime
  ORDER BY CASE
    WHEN ElapsedTime = 'Preoperative' THEN 1
    WHEN ElapsedTime = '2 Weeks' THEN 2
    WHEN ElapsedTime = '6 Weeks' THEN 3
    WHEN ElapsedTime = '3 Months' THEN 4
    WHEN ElapsedTime = '6 Months' THEN 5
    WHEN ElapsedTime = '1 Year' THEN 6
    WHEN ElapsedTime = '2 Years' THEN 7
    WHEN ElapsedTime = '3 Years' THEN 8
    WHEN ElapsedTime = '4 Years' THEN 9
    WHEN ElapsedTime = '5 Years' THEN 10
    WHEN ElapsedTime = '6 Years' THEN 11
    WHEN ElapsedTime = '7 Years' THEN 12
    WHEN ElapsedTime = '8 Years' THEN 13
    WHEN ElapsedTime = '9 Years' THEN 14
    WHEN ElapsedTime = '10 Years' THEN 15
    ELSE ElapsedTime END ASC
  </code></pre>
  
  <p>If Dr. ABC doesn't have any results w/ 'DaysElapsed' == 'Preoperative', the rest of the ORDER BY gets ignored - as in the following result:</p>
  
  <pre class="lang-sql prettyprint-override"><code>'5 Years,6 Years,7 Years,8 Years,10 Years,2 Weeks,6 Weeks,3 Months,6 Months,1 Year,2 Years,3 Years,4 Years,Unknown'
  </code></pre>
  
  <p>How do I make this ORDER BY clause more robust?</p>
  
  <p>Is there any way to include an incrementing variable in the statement, e.g. </p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT ElapsedTime, COUNT(SurgeryID) as numberOfOps
  FROM Table
  WHERE Doctor = 'Dr. ABC'
  GROUP BY Time
  var i = 0;
  ORDER BY CASE
    WHEN ElapsedTime = 'Preoperative' THEN ++i
    WHEN ElapsedTime = '2 Weeks' THEN ++i
    WHEN ElapsedTime = '6 Weeks' THEN ++i
    WHEN ElapsedTime = '3 Months' THEN ++i
    .          .          .          .
    .          .          .          .
    .          .          .          .
    .          .          .          .   
    WHEN ElapsedTime = '10 Years' THEN ++1
    ELSE ElapsedTime END ASC
  </code></pre>
  
  <p>I understand that SQL doesn't allow for such things, but that is the type of functionality which I am seeking.</p>
  
  <p>Thanks</p>