Get TOP N rows of a Group By clause in TSQL

At times we might need to get the recently added top 2 employees for each department in an organization, or else we might want to fetch latest news in all categories. Former said requirements are pretty common in nature and to solve them CTE (Common Table Expression) would be at rescue along with ROW_NUMBER() OVER PARTITION.

In the following code snippet, we have different employees (typically employee Ids) are being added with new roles and using CTE we can get the top 2 newly inserted employee into the roles table. Code is self explanatory.

USE [TestDB]
GO

/************************** Create Employee Table ****************************/

IF EXISTS(SELECT * FROM sys.sysobjects WHERE type = 'U' AND name = 'EmployeeRoles')
BEGIN
    DROP TABLE [dbo].[EmployeeRoles];
END
GO

CREATE TABLE [dbo].[EmployeeRoles](
    [EmpRoleId] [int] IDENTITY(1,1) NOT NULL,
    [EmpId] [int] NOT NULL,
    [EmpRole] [nvarchar](32) NOT NULL,
    [InsertedDate] [datetime] NOT NULL DEFAULT GETDATE()
)
GO

/********************** Enter Employee Roles Details ***********************/

INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole],[InsertedDate]) VALUES (1,'Dev','2012-12-17');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole],[InsertedDate]) VALUES (2,'Dev','2012-12-15');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole],[InsertedDate]) VALUES (3,'Sdet','2012-12-14');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole],[InsertedDate]) VALUES (4,'Dev','2012-12-01');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole],[InsertedDate]) VALUES (5,'Test','2012-12-05');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole],[InsertedDate]) VALUES (6,'Sdet','2012-12-11');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole],[InsertedDate]) VALUES (7,'Test','2012-12-10');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole],[InsertedDate]) VALUES (8,'Test','2012-12-07');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole],[InsertedDate]) VALUES (5,'Dev','2012-12-06');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole],[InsertedDate]) VALUES (6,'Sdet','2012-12-16');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole],[InsertedDate]) VALUES (7,'Dev','2012-12-10');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole],[InsertedDate]) VALUES (8,'Test','2012-12-07');
GO


/********************** Get Top 2 rows of each Roles **********************
Get the TOP 2 newly inserted rows of EmployeeRoles table. Here we use a CTE
along with ROW_NUMBER() to get the specific rows based on count.
**************************************************************************/
WITH NewEmployeeRoles AS (
     SELECT [EmpId], 
            [EmpRole], 
            [InsertedDate], 
            ROW_NUMBER() OVER(PARTITION BY [EmpRole] ORDER BY [InsertedDate] DESC) AS Emps
      FROM [dbo].[EmployeeRoles])
SELECT NER.[EmpId], NER.[EmpRole], NER.[InsertedDate]
       FROM NewEmployeeRoles NER
       WHERE NER.Emps <= 2

Result:

TopRowsGroupBy

You may also like...