Question

How to get Nth highest salaried employee in a department Note: This is one of common questions asked in interview questions.

Answer

By using row_number() function, we can solve this problem.

DECLARE @Emp TABLE
(
    EmpId INT,
    DepartmentId INT,
    Salary INT
)
INSERT INTO @Emp VALUES 
(1,1,20),(2,1,30),(3,1,25),(4,1,35),(5,2,30),(6,2,25)

declare @n int = 2

;WITH cte AS
(
        SELECT EmpId,DepartmentId,Salary,
        ROW_NUMBER() OVER(PARTITION BY DepartmentId 
						ORDER BY Salary DESC) AS Seq 
		FROM @Emp
)
SELECT EmpId,DepartmentId,Salary FROM cte 
WHERE seq = 2

But row_number() is available from SQL Server 2005 only, there are chances that interviewers might ask for solution before SQL Server 2000.

Here is an comprehensive example in SQL Server 2000

DECLARE @Emp TABLE
(
    EmpId INT,
    DepartmentId INT,
    Salary INT
)

INSERT INTO @Emp VALUES (1,1,20)
INSERT INTO @Emp VALUES (2,1,30)
INSERT INTO @Emp VALUES (3,1,25)
INSERT INTO @Emp VALUES (4,1,35)
INSERT INTO @Emp VALUES (5,2,30)
INSERT INTO @Emp VALUES (6,2,25)

DECLARE @n INT 
SET @n = 2
SELECT EmpId,DepartmentId,Salary
FROM @emp E
WHERE (SELECT COUNT(*) 
		FROM @emp 
		WHERE DepartmentId = E.DepartmentId 
		AND Salary >= E.Salary) = @n

Read More..   [32134 clicks]

Published under: SQL Server Interview Questions ·  ·  ·  ·