Get nth Highest and Lowest Salary of an Employee

Get nth Highest and Lowest Salary of an Employee: An Overview

One student of mine asked, "How can we get the nth highest and lowest salary of an employee ?". In this SQL Server tutorial, I am going to expose how can we achieve this in SQL Server. 

By 2027, 75% of tech recruiters will prioritize SQL Server expertise. Enroll in our Free SQL Server Courses Online with Certificate today!

Suppose we have the employee's name and salary as shown in the below fig.

Read More: Basics of SQL Commands

Read More - Most Asked DBMS Interview Questions

Query to get nth(3rd) Highest Salary

SELECT TOP 1 Salary AS '3rd Highest Salary'
FROM (
    SELECT DISTINCT TOP 3 Salary
    FROM Employee
    ORDER BY Salary DESC
) AS a
ORDER BY Salary ASC;

In the above code, the query first selects the top 3 distinct salaries from the "Employee" table in descending order. Then, it selects the top 1 salary from these top 3 salaries, ordering them in ascending order.

Query to get nth(3rd) Lowest Salary

SELECT TOP 1 Salary AS '3rd Lowest Salary'
FROM (
    SELECT DISTINCT TOP 3 Salary
    FROM Employee
    ORDER BY Salary ASC
) AS a
ORDER BY Salary DESC;

In the above code, this query first selects the top 3 distinct salaries from the "Employee" table in ascending order. Then, it selects the top 1 salary from these top 3 salaries, ordering them in descending order.

Do you Know?

.NET is gaining popularity day by day, especially after the release of .NET 8. .NET 8 is not only a framework version but much more than that. It redefines the way software applications are built and deployed, enabling developers to meet the evolving demands of modern computing.

.NET architects see 18% faster promotions in enterprise roles. Accelerate your ascent—sign up for .NET software architect training and unlock leadership opportunities!

Take our Sqlserver skill challenge to evaluate yourself!

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET FREE CHALLENGE