title: Compute median of column in SQL common table expression
body: <p>In MSSQL2008, I am trying to compute the median of a column of numbers from a common table expression using the classic median query as follows:</p>
  
  <pre><code>WITH cte AS
  (
     SELECT number
     FROM table
  ) 
  
  SELECT cte.*,
  (SELECT 
    (SELECT (   
      (SELECT TOP 1 cte.number  
       FROM     
       (SELECT TOP 50 PERCENT cte.number     
        FROM cte
        ORDER BY cte.number) AS medianSubquery1   
      ORDER BY cte.number DESC)  
      +   
    (SELECT TOP 1 cte.number
     FROM     
      (SELECT TOP 50 PERCENT cte.number    
       FROM cte   
       ORDER BY cte.number DESC) AS medianSubquery2   
     ORDER BY cte.number ASC) ) / 2)) AS median
  
  FROM cte
  ORDER BY cte.number
  </code></pre>
  
  <p>The result set that I get is the following:</p>
  
  <pre><code>NUMBER    MEDIAN
  x1        x1
  x1        x1
  x1        x1
  x2        x2
  x3        x3
  </code></pre>
  
  <p>In other words, the "median" column is the same as the "number" column when I would expect the median column to be "x1" all the way down.  I use a similar expression to compute the mode and it works fine over the same common table expression.</p>