# Second highest salary in sql

349 views

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?

answered by Expert (5,813 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.

commented by Yassine Khachlek
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
...
commented by anonymous
SELECT salary FROM emploree ORDER BY SALARY DESC
commented by anonymous
Select salary from table where salary < (select salary from table order by salary desc limit 1) order by salary desc limit 1
answered by Expert (5,030 points)
edited

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

## Related Questions

1 answer 855 views
0 answers 422 views
0 answers 124 views