Get top X from each category SQL SERVER 2005 +

Get top 5 from each category

simple logic use Row_Number function with partition by the category for which you want to create group.
 ROW_NUMBER() OVER (PARTITION BY CategoryID order by CategoryID  )

Step 2: Now Row_Number will add numbers on each category, kept the result in temp table or CTE.

Step 3: Now use select statement to get records where rowNum greater than X.


;with Rohi  as
(
 select
 TestID,CategoryID,
 ROW_NUMBER() OVER (PARTITION BY CategoryID order by CategoryID  ) AS  'RowNum'

  from Test
  )
  select * from Rohi where RowNum <=5