Optimized top 2 ways to find 2nd or Nth highest salary in SQL Server.

Today we will understand how to find the Nth highest salary in an optimized way and the Nth highest salary in each department using the SQL server.

There are lots of the solution to find Nth highest salary and department wise Nth highest salary but the question is that what is the optimized solution to find Nth salary.

This is the most popular interview question in the SQL Server database. Don't worry I'm sharing the best optimized solution to find the Nth highest salary from the Employee table and also find the Nth highest salary in each department.

[Top 100+ popular SQL Interview Questions and Answers Blog.]

There are two optimized solutions to finding nth highest salary from the Employee table. One is using CTE (Common Table Expression) and the second is using a subquery. Let's see both solutions with examples.

First, create the department table and insert some dummy data.

CREATE TABLE [dbo].[Department]

(

       DeptId INT IDENTITY(1,1) PRIMARY KEY,

       DeptName VARCHAR(100)

);

GO

INSERT INTO [dbo].[Department] (DeptName) VALUES ('Computer');

INSERT INTO [dbo].[Department] (DeptName) VALUES ('Research');

INSERT INTO [dbo].[Department] (DeptName) VALUES ('Sales');

INSERT INTO [dbo].[Department] (DeptName) VALUES ('Accouting');

Now, We create the Employee table and insert some dummy data.

CREATE TABLE [dbo].[Employee]

(

       EmpId INT IDENTITY(1,1) PRIMARY KEY,

       EmpName VARCHAR(20),

       ManagerId INT NULL,

       Salary MONEY,

       DeptId INT FOREIGN KEY REFERENCES [dbo].[Department](DeptId)

);

GO

INSERT INTO [dbo].[Employee] (EmpName,ManagerId,Salary,DeptId) VALUES ('Rajesh',NULL, 60000,1);

INSERT INTO [dbo].[Employee] (EmpName,ManagerId,Salary,DeptId) VALUES ('Rohan',1, 50000,1);

INSERT INTO [dbo].[Employee] (EmpName,ManagerId,Salary,DeptId) VALUES ('Janak',NULL, 70000,2);

INSERT INTO [dbo].[Employee] (EmpName,ManagerId,Salary,DeptId) VALUES ('Mohan',3, 50000,2);

INSERT INTO [dbo].[Employee] (EmpName,ManagerId,Salary,DeptId) VALUES ('Rahul',NULL, 40000,3);

INSERT INTO [dbo].[Employee] (EmpName,ManagerId,Salary,DeptId) VALUES ('Pragnesh',NULL, 40000,4);

 

Hope you can successfully able to create both the above tables in your database now we go for example.

Actual employee data in the employee table.

SELECT * FROM Employee ORDER BY Salary DESC

1) Find the 2nd highest salary or the Nth highest salary using CTE (Common Table Expression).

Example of 2Nd highest salary.

DECLARE @Rank INT=2;

 

WITH EmployeeCte AS

(

       SELECT DENSE_RANK() OVER (ORDER BY Salary DESC) As SalaryRank,* FROM Employee

)

SELECT * FROM EmployeeCte WHERE SalaryRank=@Rank

Result

Example of Nth highest salary with CTE.

DECLARE @Rank INT=N;

 

WITH EmployeeCte AS

(

       SELECT DENSE_RANK() OVER (ORDER BY Salary DESC) As SalaryRank,* FROM Employee

)

SELECT * FROM EmployeeCte WHERE SalaryRank=@Rank

In the above two examples, you can clearly be seen that if we set the @Rank=2 it will return 2nd highest salary of the employee or we set 3 then it will return 3rd highest salary. This is 

1) Find the 2nd highest salary or the Nth highest salary using a subquery.

DECLARE @Rank INT=2;

SELECT * FROM Employee Emp1 WHERE (@Rank-1)=

(

SELECT COUNT(DISTINCT(Salary)) FROM Employee Emp2 WHERE Emp2.Salary>Emp1.Salary

)

Result

If you can see here we have got the same result of the query, but the question is that which is an optimized solution, so as per my suggestion option 1 using CTE is the best option and also easy to implement for the developers. Because the 2nd solution traverses each row of the employee table and compares with the inner query so it will take much time compared to solution 1.

Now we find nth highest salary department wise from the employee table.

The nth highest salary department wise

Example of 2nd highest salary department wise

DECLARE @Rank INT=2;

WITH EmployeeCte AS

(

       SELECT DENSE_RANK() OVER (PARTITION BY DeptId ORDER BY Salary DESC) As SalaryRank,* FROM Employee

)

SELECT

       E.EmpId

       ,E.EmpName

       ,E.Salary

       ,D.DeptName

FROM

       EmployeeCte E

       INNER JOIN Department D ON E.DeptId=D.DeptId

WHERE 

        SalaryRank=@Rank

Result

Using this query you can find 3rd, 4th,5th and Nth highest salary from the employee table and also you can find the department wise highest salary in an optimized way. Also, you can find the Nth lowest salary by changing the order with ASC.

I hope you have liked this article to the understanding of Find the Nth highest salary or 2nd highest salary and Department wise Nth highest salary.

Please feel free to comment if you have found anything incorrect, or you want to share more information about the topic discussed above.

Please Share this article so it will help to another one because “Helping millions grow better”.

Follow my blog https://mydotnetguru.blogspot.com to read the more interesting technical articles.

Next Recommended Article:

1. Different ways to remove or delete duplicate rows from a SQL Table.
   
2. When to use abstract class and interface in C#?
3. Async and Await in C#
   
4. Top 100+ popular ASP.Net MVC Interview Questions and Answers.
   
5. Top 100+ popular SQL Interview Questions and Answers Blog.
6. Top 100+ popular C# Interview Questions and Answers.
   
7. @Input, @Output decorator and EventEmitter class in Angular.
8. Dependency Injection and types of dependency injection.