Monday 3 September 2018

SQL Query to find nth Highest salary

In this post, I will explain a SQL Query to find nth Highest salary. 

In below query subquery returns the count of a distinct salary greater than current employee salary. Then the result of subquery will be compared with @N and record with subquery result equal @N will be returned as result.

DECLARE @N AS int = 2

SELECT * FROM Employee_Master EM1
WHERE @N  = (SELECT COUNT(DISTINCT(EM2.Salary)) FROM Employee_Master EM2 WHERE EM2.Salary > EM1.SALARY)

Table Script-

CREATE TABLE Employee_Master(
EmployeeId int IDENTITY(1,1) ,
EmployeeName varchar(100) NULL,
Salary decimal(18, 2) NULL,

0 comments:

Post a Comment

Please do not enter any spam link in the message box.