Logicsense

How to Find Nth/Second Highest and Lowest Salary in SQL

We will explore the answer with a series of scenarios and questions asked in relation to finding the highest, lowest, and nth highest salary.

+----+---------+-----------+
| Id | Amount  |  Name     |
+----+---------+-----------+
| 1  | 1100    |   Peter   |
| 2  | 1200    |   Sachin  |
| 3  | 1300    |   Roberts |

| 4 | 1400   |   Shyam  |
+----+---------+-----------+

suppose table name is tbl_TeacherPay

(A) Find the Employee with the Highest Salary

SELECT Name, Amount

FROM

(

Select Name, Amount, ROW_NUMBER() OVER(Order by Amount Desc) as Salary_Order

from   tbl_TeacherPay

) DT

WHERE DT. Salary_Order = 1 ;

(B)Finding the Employee with the Nth Highest Salary

So Query is

SELECT Name, Amount

FROM

(

Select Name, Amount, ROW_NUMBER() OVER(Order by Amount Desc) as Salary_Order

from   tbl_TeacherPay

) DT

WHERE DT. Salary_Order = 3 ;

Result

+----+---------+-----------+
| Id | Amount  |  Name     |
+----+---------+-----------+
| 1  | 1200    |  Sachin   |

+----+---------+-----------+

(C)Finding the Employee with the Lowest Salary

SELECT Name, Amount

FROM

(

Select Name, Amount, ROW_NUMBER() OVER(Order by Amount asc) as Salary_Order

from   tbl_TeacherPay

) DT

WHERE DT. Salary_Order = 1 ;

(D)Find the Employee with the Highest Salary When There Is a Tie (Two employees both have the highest salary and the number is the same)

SELECT Name, Amount

FROM

(

Select Name, Amount, ROW_NUMBER() OVER(Order by Amount desc) as Salary_Order

from   tbl_TeacherPay

) DT

WHERE DT. Salary_Order = 1 ;