Write a SQL query to get the second highest salary from the Employee table

Write a SQL query to get the second highest salary from the Employee table

Example

Table: Employee            
    ID  Salary  
    1   400 
    2   200 
    3   300 
    4   500 
Result: Second highest salary is 400

MySQL: Using max function

select max(e.Salary) as highestSalary  from Employee e
where e.Salary not in (select max(ee.salary
from Employee ee);

Oracle: Using row_number()

select Salary as HighestSalary  from (
select e.*, row_number() over (order by Salary desc) as row_num 
from Employee e
) where row_num = 2;

Oracle: Using rank() -- Remove duplicate row's

select Salary as HighestSalary from (
select e.*, rank() over (order by Salary desc) as rank 
from Employee e
) where rank = 2;


No comments:

Post a Comment