Browse Tutorials
Get nth Highest and Lowest Salary of an Employee

Get nth Highest and Lowest Salary of an Employee

18 Mar 2024
Intermediate
237K Views
3 min read

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.

Read More: SQL Server Interview Questions and Answers

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

Read More: Basics of SQL Commands

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.

Summary

In this article, I explained how you can get the highest and lowest salary of an employee. I hope after reading this article you will be able to do this. If you want to gain a practical understanding, you can enroll in our SQL Server Course.

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.

Therefore, if you want to upskill yourselves and stand out from others consider our following training programs on .NET.

Take our free sqlserver skill challenge to evaluate your skill

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

GET CHALLENGE

Share Article
Batches Schedule
About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
Accept cookies & close this