Select Top N Rows Per Group – MS SQL. This code below works with MS SQL 2005 or newer.

–Create a temp table

DECLARE @temp table
(
id int,
groupID int,
groupName varchar(150)
)

–insert sample data for testing

INSERT INTO @temp VALUES(1,1,’A')
INSERT INTO @temp VALUES(2,1,’A')
INSERT INTO @temp VALUES(3,1,’A')
INSERT INTO @temp VALUES(4,1,’A')
INSERT INTO @temp VALUES(5,2,’B')
INSERT INTO @temp VALUES(6,2,’B')
INSERT INTO @temp VALUES(7,2,’B')
INSERT INTO @temp VALUES(8,2,’B')
INSERT INTO @temp VALUES(9,2,’B')
INSERT INTO @temp VALUES(10,2,’B')
INSERT INTO @temp VALUES(11,3,’C')
INSERT INTO @temp VALUES(12,3,’C')

–With Common Table Expression sort them grouped by groupId

WITH CTE AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY groupID ORDER BY ID) AS ‘RowNo’,
ID, groupID, groupName
FROM @TT
)

–Select first 2 items per groupId

SELECT ID,CategoryGroupID,CategoryGroupName
FROM CTE
WHERE RowNo <= 2