Like somuch.com on Facebook Share somuch.com on Facebook somuch.com on Twitter Reddit LinkedIn Pinterest
Newest Tips & Tricks...

Home :: Tips & Tricks :: Microsoft SQL Server

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: http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Recommended Links...