SQL: Find nth Highest and Minimum Salary From a Table Part1
***How to find nth Highest/Minimum Salary from a table?
***Note: Where n can be any number.
MAX(Column name) - Will return max value of Column Name
MIN(Column name) - Will return min value of Column Name
/* Minimun/Maximum Salary from Employee Table*/
Select * From Employee
Where SALARY IN
(Select MAX(Salary) From Employee);
Select * From Employee
Where SALARY IN
(Select MIN(Salary) From Employee);
/*Second highest/minimum Salary From a Table*/
Select * From Employee
Where SALARY IN
(
Select MAX(SALARY) From Employee
Where SALARY NOt IN
(Select MAX(Salary) From Employee));
Select * From Employee
Where SALARY IN
(
Select MIN(SALARY) From Employee
Where SALARY NOt IN
(Select MIN(Salary) From Employee));
/* Nth highest/minimum salary from a table*/
Method 1 - Using Subquery
Select * From Employee;
101 Rajan 12000
103 Karan 33000
102 Pritesh 72000
105 Akif 200000
106 Ravi 999999
104 Manu 1200000
/*For each records processed by outer query, my inner query will be executed and will return how many records are less than the current salary*/
***Minimum Salary
Select * From Employee T1
Where
(n-1) = (Select Count(DISTINCT(T2.SALARY)) From Employee T2 Where T1.SALARY [Greater than or Equal To] T2.SALARY);
***Minimum Salary
Select * From Employee T1
Where
(n-1) = (Select Count(DISTINCT(T2.SALARY)) From Employee T2 Where T1.SALARY [Less than or equal to] T2.SALARY);
Method 2 - Using ROW_NUMBER
Select * From Employee;
101 Rajan 12000
103 Karan 33000
102 Pritesh 72000
105 Akif 200000
106 Ravi 999999
104 Manu 1200000
/* nth Maximum Salary */
Select ID,NAME,SALARY From
(
Select ID,NAME,SALARY,
ROW_NUMBER() Over (Order By SALARY DESC) Rn
From Employee
) Where Rn=3;
/* nth Minimum Salary */
Select ID,NAME,SALARY,
ROW_NUMBER() Over (Order By SALARY) Rn
From Employee;
Select ID,NAME,SALARY From
(
Select ID,NAME,SALARY,
ROW_NUMBER() Over (Order By SALARY) Rn
From Employee
) Where Rn=3;
Method 3 - Using Rank/Dense_rank
Select * From Employee;
101 Rajan 12000
103 Karan 33000
102 Pritesh 72000
105 Akif 200000
106 Ravi 999999
104 Manu 1200000
/* nth Maximum Salary */
Select ID,NAME,SALARY From
(
Select ID,NAME,SALARY,
RANK() Over (Order By SALARY DESC) Rn
From Employee
) Where Rn=3;
/* nth Minimum Salary */
Select ID,NAME,SALARY,
RANK() Over (Order By SALARY) Rn
From Employee;
Select ID,NAME,SALARY From
(
Select ID,NAME,SALARY,
RANK() Over (Order By SALARY) Rn
From Employee
) Where Rn=3;
Select * From Employee;
101 Rajan 12000
103 Karan 33000
102 Pritesh 72000
105 Akif 200000
106 Ravi 999999
104 Manu 1200000
/* nth Maximum Salary */
Select ID,NAME,SALARY From
(
Select ID,NAME,SALARY,
DENSE_RANK() Over (Order By SALARY DESC) Rn
From Employee
) Where Rn=3;
/* nth Minimum Salary */
Select ID,NAME,SALARY,
DENSE_RANK() Over (Order By SALARY) Rn
From Employee;
Select ID,NAME,SALARY From
(
Select ID,NAME,SALARY,
DENSE_RANK() Over (Order By SALARY) Rn
From Employee
) Where Rn=3;
***How to find nth Highest/Minimum Salary from a table Department wise?
Select * From Employee;
/* nth Maximum Salary */
Select DEPTNAME,ID,NAME,SALARY From
(
Select DEPTNAME,ID,NAME,SALARY,
RANK() Over (PARTITION BY DEPTNAME Order By SALARY DESC) Rn
From Employee
) Where Rn=1;
/* nth Minimum Salary */
Select DEPTNAME,ID,NAME,SALARY From
(
Select DEPTNAME,ID,NAME,SALARY,
RANK() Over (PARTITION BY DEPTNAME Order By SALARY) Rn
From Employee
) Where Rn=1;
Select * From Employee;