NthLargestSalary

Find Nth largest salary from a table, for example in below example 3rd largest salary has been queried from SQL database...

Let's take a table TEST as below:

Name	Dept	Salary
Ram	Marketing	1700
Hari	Technical	1000
Gopal	Electrical	1100
Kris	Driver	900
Muni	ComputerTech	2500
Sam	Dept Head	3000
Sita	Sales	1250
Geeta	HR	1900

 

Select * from Test order by Salary desc ;

Name	Dept	Salary
Sam	Dept Head	3000
Muni	ComputerTech	2500
Geeta	HR	1900
Ram	Marketing	1700
Sita	Sales	1250
Gopal	Electrical	1100
Hari	Technical	1000
Kris	Driver	900

1st way to get the 3rd largest salary:

Select Name, Dept, max(salary) from Test where Salary < (Select max(Salary) from Test where Salary < (Select max(Salary) from Test)); NOTE: This does not give correct result when there is duplicate values

Name	Dept	max(salary)
Geeta	HR	1900

Get 3rd LOWEST salary:

Select Name, Dept, min(salary) from Test where Salary > (Select min(Salary) from Test where Salary > (Select min(Salary) from Test)); NOTE: This does not give correct result when there is duplicate values

Name	Dept	min(salary)
Gopal	Electrical	1100

 

2nd way to get the 3rd largest salary:

Select name, dept, min(salary) as ThirdMINSal from (Select * from (Select * from Test order by Salary desc) where rownum<4) ;  NOTE: This is an old way in ORACLE and This does not give correct result when there is duplicate values

Select name, dept, min(salary) as ThirdMINSal from (Select * from (Select * from Test order by Salary desc) FETCH FIRST 3 ROWS ONLY); NOTE: This is new way in Oracle and This does not give correct result when there is duplicate values

 

3rd way to get the 3rd largest salary:

Using DENSE_RANK() ---SQL query in progress

 

In MySQL

Select name, dept, min(salary) as ThirdMINSal from (Select * from (Select * from Test order by Salary desc) limit 3); NOTE: This does not give correct result when there is duplicate values

Name Dept ThirdMINSal Geeta HR 1900

EXAMPLE 2(with duplicate value):

Select * from TableUnitTest order by salary desc;

Name Dept Salary Hari Mechanical 1200 Sam Admin 1100 Ram Technical 1000 Govind HR 1000 Murali Driver 900 Raju Helper 800
Select * from TableUnitTest  where salary = (Select min(Salary) from   (Select salary from ( Select Distinct salary from TableUnitTest order by salary desc ) limit 3)); Name Dept Salary Ram Technical 1000 Govind HR 1000