Interview Question: Nth Highest Salary

1.To Find the maximum salary first create an Employee table:

create table Employee (

    EmpID int,

    EmpName varchar(255),

    EmpPhNum int,

    EmpAge int,

    EmpAddress varchar(255),

    EmpSalary int

);

2.Then insert data into the table:

insert into Employee values(1,'John',989756,21,'Street 1,LF',10000);

insert into Employee values(2,'JohnP',989757,24,'Street 2,GH',50000);

insert into Employee values(3,'Peter',989456,27,'Street 3,CF',30000);

insert into Employee values(3,'Peter',989456,27,'Street 3,CF',40000);

insert into Employee values(4,'Johnson',983656,45,'Street 4,WE',75000);

insert into Employee values(5,'Johny',989866,25,'Street 5,DF',85000);

insert into Employee values(6,'Johny',989866,35,'Street 5,DF',60000);

3.Check whether all the data is inserted properly into the table

select * from Employee;

4.Get the maximum salary in the table using the max function

select max(EmpSalary) from Employee;

5.To get the second highest salary in a table, we need to use the inner query as below:

For second highest salary we need to use 1 inner query.

select max(EmpSalary) from Employee where EmpSalary<(select max(EmpSalary) from Employee);

For 3rd highest salary we need to use 2 inner query

select max(EmpSalary) from Employee where EmpSalary<(select max(EmpSalary) from Employee where EmpSalary < (select max(EmpSalary) from Employee));

For nth highest salary we need to use n-1 inner query

6.We can simplify this with LIMIT option

select * from Employee LIMIT 2;

It will list the first 2 rows

For second higest salary

select EMPSalary from Employee order by EMPSalary desc LIMIT 2-1,1; 

For third higest salary

select EMPSalary from Employee order by EMPSalary desc LIMIT 3-1,1; 

For nth higest salary

select EMPSalary from Employee order by EMPSalary desc LIMIT n-1,1;