title: SQL Query: Find highest revenue month/year for a customer
body: <p>I'm looking to query the database to find highest revenue month for all the customers in the system. I have got the query working to pull customers monthly revenue from all the years for which the data is present. But I'm struggling to figure out how to get highest revenue month-year from this data.</p>
  
  <p>The database is SQL Server 2008 R2. 
  The columns are: Customer name, Year, Month, and Revenue.
  I even tried using Row_Number() and tried partitioning by customer name/year and ordering by revenue. But it didn't work. Maybe I'm making some mistake there.</p>
  
  <p>Here's how I tried to build the base query.</p>
  
  <pre><code>Select Customer, Year(orderdatetime) as Year, Month(orderdatetime) as Month, SUM(Revenue)
  From Orders
  Group By Customer, Year(orderdatetime), Month(orderdatetime)
  </code></pre>
  
  <p>This is how I tried to use Row_Number()</p>
  
  <pre><code>WITH Max_Revenue AS
  (
  Select Customer, Year(orderdatetime) as Year, Month(orderdatetime) as Month, SUM(Revenue), RowNumber = ROW_NUMBER() OVER(PARTITION By Year Order By Revenue DESC)
      From Orders
      Group By Customer, Year(orderdatetime), Month(orderdatetime)
  )
  Select Max_Revenue.Customer, Max_Revenue.Year, Max_Revenue.Month, Max_Revenue.Revenue
  From Max_Revenue
  Where Max_Revenue.RowNumber = 1
  Order By Max_Revenue.Customer asc
  </code></pre>
  
  <p>The data I get back is like:</p>
  
  <pre><code>Customer   Month   Year  Revenue
    ABC        2       2012  100
    ABC        3       2013  150
    ABC        5       2012  200
    XYZ        4       2011  500
    XYZ        6       2012  650
    XYZ        7       2012  800
  </code></pre>
  
  <p>What I want as the output is</p>
  
  <pre><code> Customer   Month   Year  Revenue
   ABC        5       2012  200
   XYZ        7       2012  800
  </code></pre>
  
  <p>So every customer's best month and respective year in terms of revenue.        </p>