How to retrieve the top N rows for each group

Introduction

This T-SQL sample script illustrates how to retrieve the top N rows for each group. The first solution can be used with SQL Server 2000 and upward versions, the second solution applies to SQL Server 2005 and upward versions that with common table expression feature and Row_Number() function.

Scenarios

For example, there is a sample table that has three columns and eight rows:

ProductID CategoryID UnitPrice

----------- ----------- -----------

1 1 100

2 1 25

3 1 46

4 1 22

5 2 11

6 2 44

7 2 21

8 3 15

If we want to see the three of the most expensive (UnitPrice) products for each category in the table, the expected result should be:

ProductID CategoryID UnitPrice

----------- ----------- -----------

1 1 100

3 1 46

2 1 25

6 2 44

7 2 21

5 2 11

8 3 15

Script

In order to help us understand the process and the function of this action more thorough, we divide the whole script code into three parts.
Part1. Define one table variable and insert eight rows into it
Part2. The solution for SQL Server 2000 and upward versions
Part3. The solution for SQL Server 2005 and upward versions which with the common table expression feature and Row_Number() function.

Here are some code snippets for your references. To get the complete script sample, please click the download button at the beginning of this page.

SQL

Edit|Remove

--Solution for SQL Server 2000 and later 
 
SELECT ProductID, 
       CategoryID, 
       UnitPrice 
FROM   @Products p1 
WHERE  ProductID IN (SELECT TOP 3 ProductID 
                     FROM   @Products p2 
                     WHERE  p1.CategoryID = p2.CategoryID 
                     ORDER  BY UnitPrice DESC)
ORDER BY CategoryID, UnitPrice DESC

--Solution for SQL Server 2000 and later  
  
SELECT ProductID,  
       CategoryID,  
       UnitPrice  
FROM   @Products p1  
WHERE  ProductID IN (SELECT TOP 3 ProductID  
                     FROM   @Products p2  
                     WHERE  p1.CategoryID = p2.CategoryID  
                     ORDER  BY UnitPrice DESC) 
ORDER BY CategoryID, UnitPrice DESC

Additional Resource

Using Common Table Expressions
ROW_NUMBER (Transact-SQL)