Second highest salary in sql

Question

In a table of Employee Select n^th 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?

Answer 1

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 n^th 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. 

Comments

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
...

---

SELECT salary FROM emploree ORDER BY SALARY DESC

---

Select salary from table where salary < (select salary from table order by salary desc limit 1) order by salary desc limit 1

Answer 2

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