How to find nth highest salary in SQL

6
1673

In this article of q Lab, we will learn how to find nth highest salary in sql or nth lowest salary in sql. We will look into optimizing query factor also for the better performance of the query. Let’s start with finding Maximum salary first and then with our deducing capability will reveal the nth concept.

how to find nth highest salary

To Find Maximum Salary –

Method 1 : Using Aggregation fuunction

SELECT MAX (Salary)
FROM employee_phpring;

To Find 2nd Highest Salary –

Method 1 : Using NOT IN

SELECT MAX (Salary)
FROM employee_phpring
WHERE Salary NOT IN (
SELECT MAX (Salary) FROM employee_phpring );

Method 2 : Using NOT EQUAL “<>” Operator

SELECT MAX (Salary)
FROM employee_phpring
WHERE Salary <>  (
SELECT MAX (Salary) FROM employee_phpring );

To Find nth Highest Salary –

Method 1 : Using Co-related Sub query Concept.

SELECT *
FROM employee_phpring Table1
WHERE (N-1) = (
SELECT COUNT (DISTINCT (Table2.Salary)
)
FROM employee_phpring Table2
WHERE Table2.Salary > Table1.Salary;

NOTE :- You can probably see that Table1 and Table2 are just aliases for the same Employee table – it’s like we just created 2 separate clones of the Employee table and gave them different names.

As we learned above that inner query executes every time, one row of outer query is processed, this brings a lot of performance overhead, specially if the number of rows are too big. We can use DB specific keywords like TOP keyword to optimize the performance of our query.

Method 2 : Using TOP keyword in SQL

SELECT TOP 1 Salary
FROM (
SELECT DISTINCT TOP N Salary
FROM employee_phpring
ORDER BY Salary DESC
) AS Emp
ORDER BY Salary

Conclusion :-

  1. Query to Find 2nd Highest Salary

SELECT TOP 1 Salary as ‘2nd Highest Salary’
FROM  (
SELECT DISTINCT TOP 2 Salary from employee_phpring ORDER BY Salary DESC
)
AS Emp
ORDER BY Salary ASC

2.  Query to Find 2nd Lowest Salary

SELECT TOP 1 Salary as ‘2nd Lowest Salary’
FROM  (
SELECT DISTINCT TOP 2 Salary from employee_phpring ORDER BY Salary ASC
)
AS Emp ORDER BY Salary DESC

In this article we learn how to find nth highest salary or nth lowest salary of an employee in SQL. We can also use ROW_NUMBER() or LIMIT keywords for optimizing performance of the query depending upon the database we use.Please posr your valuable comments so that we can list more ways of finding nth highest salary.

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 9.6/10 (25 votes cast)
VN:F [1.9.22_1171]
Rating: +21 (from 23 votes)
How to find nth highest salary in SQL, 9.6 out of 10 based on 25 ratings