title: How to ORDER BY in SQL PIVOT
body: <p>I currently have this query using PIVOT generating a table like this:</p>
  
  <pre><code>  USER  |  DEC  |  NOV  |  OCT
  ---------------------------------
    bob   |   3   |   5   |   2
    jon   |   7   |   0   |   1 
    tim   |   4   |   2   |   6
  </code></pre>
  
  <p>What I would like to do but it looks like a stretch is to <code>ORDER BY</code> the results by the <code>DEC</code> value descending.</p>
  
  <p>This is the query:</p>
  
  <pre><code>with Mth (st, nd) as ( 
    select DATEADD (M, datediff (m, 0,'2012-09-01'), 0), 
           DATEADD (M, DATEDIFF (m, 0, '2012-09-01') + 1, 0)   
    union all 
    select DATEADD (m, 1, st), 
           DATEADD (m, 1, nd) 
    from Mth 
    where nd &lt;= DATEADD (m, datediff (m, 0, getdate()), 0)
  ) 
  select * 
  from 
  ( 
    select MONTH(Mth.st) Month, 
        U.USER, 
        COUNT(S.QRY_ID) Searches 
    FROM Mth 
    LEFT JOIN SEARCHES S 
      on Mth.st &lt;= S.CREATED 
      and Mth.nd &gt; S.CREATED 
    LEFT JOIN MEMBERS U 
      on U.AID = S.AID 
    GROUP BY YEAR(Mth.st), MONTH(Mth.st), U.HOLDER_LOGIN
  ) src 
  pivot 
  ( 
    sum(searches) 
    for month in ([12],[11],[10]) 
  ) piv
  </code></pre>
  
  <p>Doing <code>piv ORDER BY piv.Searches</code> gives an error so is it possible to specify the column?</p>