Rediscovering RANK() – Selecting the TOP N Rows for each Group or Category
There may be times when you need to get the top 10 (or n) rows from each group of your dataset to satisfy a business requirement. There are a few ways this can be done, but none so elegant as when you use RANK().
RANK() effectively applies a ranking to each rows in your dataset, based on a specified grouping and order. This can then be used to select the Top n rows by using an appropriate where clause. Here’s an example:
1: USE Adventureworks
2: go
3:
4: WITH OrdersCTE
5: AS
6:
7: (
8: Select
9: salespersonid,
10: customerid,
11: SUM(subtotal) Total,
12: RANK() OVER(PARTITION BY salespersonid ORDER BY SUM(subtotal) desc) Ranking
13: from Sales.SalesOrderHeader
14: Where
15: salespersonid is not null
16: Group by
17: customerid,
18: salespersonid
19: )
20:
21: Select
22: salespersonid,
23: customerid,
24: Total,
25: Ranking
26: From OrdersCTE
27: Where
28: Ranking <= 10
In the above example, I needed to find the top 10 Customers for each Sales Person based on the sum of all order subtotals (using AdventureWorks 2005). Here are the results:
Ranking Results
You can read more about the RANK() function here, and for more ranking functions see DENSE_RANK(), NTILE() & ROWNUMBER().
[...] This post was mentioned on Twitter by Jungchan Hsieh, Jason Coombes and Luke Hayler, DotNetTips. DotNetTips said: RT @JungchanHsieh: Rediscovering RANK() – Selecting the TOP N Rows for each Group or Category http://cli.gs/AR9qh #tips via @lukehayler [...]