title: Conditional Max in SQL
body: <p>I have to following query in SQL Server:</p>
  
  <pre><code>SELECT EmployeeID, 
         TotalQuantity AS TotalQty, 
         TotalSales, 
         MAX(CASE WHEN MonthNumber = MAX(MonthNumber)
             THEN TotalSales END) as RecentMonthSale
  FROM vwSales 
  GROUP BY EmployeeID, TotalQuantity , TotalSales
  </code></pre>
  
  <p>Bu it gives me the error:</p>
  
  <blockquote>
  <pre><code>Cannot perform an aggregate function on an expression
  containing an aggregate or a subquery.
  </code></pre>
  </blockquote>
  
  <p>Input View is as follows:</p>
  
  <pre><code>EmployeeID    TotaSales MonthNumber
    1             4000      1
    1             6000      2
    2             8500      1
    2             6081      2 
  </code></pre>
  
  <p>Desired output:</p>
  
  <pre><code>EmployeeID    TotalSale     RecentMonthSale
    1            10000            6000
    2            14581            6081
    3            11458            1012 
  </code></pre>
  
  <p>I want following column in my output <code>EmployeeID, TotalQuantity TotalSale RecentMonthSale</code> My View has the following column <code>EmployeeID TotalSale,TotalQuantity, MonthNumber</code>.</p>