+5 votes
1.6k views
in PHP Interview Question by

In a table of Employee Select nth highest salary from the table.

Table name is Employee and columns are EmpID and Sal.

 

Frequently asked this question in an interview,How you will explain it?

2 Answers

0 votes
by Expert (5.9k points)

Let's start from a Normal query if you have to find max salary from that table that would be .
 

select MAX(Salary) from Employee;

And now if we have to find sec highest salary from the table , see the query : 

select MAX(Salary) from Employee where Salary NOT IN (select MAX(Salary) from Employee);

And query for nth highest salary  :

SELECT * 

FROM Employee Emp1 

WHERE (N-1) = ( SELECT COUNT(DISTINCT(Emp2.Salary)) 

FROM Employee Emp2 

WHERE Emp2.Salary > Emp1.Salary)

After seeing this query you may be get confused because of Emp1 and  Emp2 , Let me tell you about it.

Emp1 and Emp2 are nothing but alias of  Employee table. I hope you understood. 

0
by (100 points)
Select sal from (select distinct sal  as sal from employee order by sal desc limit n) order by sal limit 1
0
by
Order by salary desc limit 1, 1
0
by
It's not simple like that !

SELECT Sal FROM Employee ORDER BY Sal DESC LIMIT N-1,1

For n =1:
SELECT Sal FROM Employee ORDER BY Sal DESC LIMIT 0,1
For n = 5:
SELECT Sal FROM Employee ORDER BY Sal DESC LIMIT 4,1
For n = 14:
SELECT Sal FROM Employee ORDER BY Sal DESC LIMIT 13,1
...
0
by
SELECT salary FROM emploree ORDER BY SALARY DESC
0
by
Select salary from table where salary < (select salary from table order by salary desc limit 1) order by salary desc limit 1
0 votes
by Expert (5.1k points)
edited by

Very simple solution to get the n'th salary with the help of limit parameter.

select sal from employee order by sal desc limit n-1,1;

Where n is the nth number.

eg : If you want to find the 3rd max salary then you will use.


n = 3;

n-1 = 2

 

select sal from employee order by sal desc limit 2,1;

 

Hope you get the idea , By this you can get nth number of max salaries

Not a Member yet?

Ask to Folks Login

My Account

Your feedback is highly appreciated