Process N record from each group | SQL Server

 

ROW_NUMBER() function in SQL Server is very useful special in case when you want to process N records from a specific group. This function has been around since SQL Server 2005 and at its core, provides a way to provide sequential numbering for rows returned by a query.

As well as sequential numbering, ROW_NUMBER() provides a way to partition the results returned so this suited me just fine

Table Structure 

Query

SELECT ServiceMaster.ID, ServiceMaster.ServiceName, ServiceCategory.CategoryName, ServiceCategory.ID AS Expr1 FROM ServiceMaster INNER JOIN ServiceCategory ON ServiceMaster.ID = ServiceCategory.ServiseMaster ORDER BY ServiceMaster.ServiceName

Output

I want 3 records from each service master group

Solution : 

WITH MyRowSet AS ( SELECT ServiceMaster.ID, ServiceMaster.ServiceName, ServiceCategory.CategoryName, ServiceCategory.ID as cid, ROW_NUMBER() OVER (PARTITION BY ServiceMaster.ServiceName ORDER BY ServiceMaster.ServiceName) AS RowNum FROM ServiceMaster INNER JOIN ServiceCategory ON ServiceMaster.ID = ServiceCategory.ServiseMaster ) SELECT * FROM MyRowSet WHERE RowNum < 4

Result

Next Topic