title: Returning top group of rows by aggregate rank
body: <p>I want to select a top group of rows, based on the rank of an aggregate of one of the columns. As an example, lets say I have the following query:</p>
  
  <pre><code>SELECT CustomerID, ItemID, Price * Qty as Revenue
  FROM InvoiceTable
  WHERE InvoiceDate between @StartDate, @EndDate
  </code></pre>
  
  <p>Pretty straight forward here, a query that returns every line item of every invoice between a set of dates. Now, what I would like to do is limit this so that only the items belonging to the top 10 Customers, based on the sum of Revenue are returned. Is there a straight forward way to do this?</p>
  
  <p>I can always dump the results of the above query into a temporary table, run a second pass to compute the sum for each customer and store that in another column of the temporary table, and then select using a RANK OVER clause, but this seems a little convoluted, and I was hoping there was a more straight forward way to accomplish my goal.</p>
  
  <p>To clarify, the query above is very very simplified, and it is not going to be practical to pre-compute a list of the top 10 clients before actually generating the list (the actual query consists of a number of unions and other conditions), which is why I am looking to either limit it in the query itself, or as a post-computational step.</p>