How to get top N rows for each group?

Declare @myTable table (cat varchar(100), subcat varchar(100), rank int )
Insert Into @myTable
Select 'A', 'a', 2
Union All Select 'A', 'b', 52
Union All Select 'A', 'c', 14
Union All Select 'A', 'd', 46
Union All Select 'A', 'e', 37
Union All Select 'A', 'f', 95
Union All Select 'A', 'g', 73
Union All Select 'A', 'h', 67
Union All Select 'A', 'i', 80
Union All Select 'A', 'j', 03
Union All Select 'B', 'a', 18
Union All Select 'B', 'b', 44
Union All Select 'B', 'c', 52
Union All Select 'B', 'd', 60
Union All Select 'B', 'e', 28
Union All Select 'B', 'f', 06
Union All Select 'B', 'g', 70
Union All Select 'B', 'h', 90
Union All Select 'B', 'i', 89
Union All Select 'B', 'j', 31
declare @n int
Set @n = 5
Select Cat, subCat, rank
From @myTable as A
Where (Select count(1) From @myTable Where cat=A.cat and rank <= A.rank)<=@n
Order By Cat, Rank Desc

Source: https://weblogs.sqlteam.com/mladenp/2005/08/01/7421/
Viewed 18385 times