Hey there! any question in your mind? Ask It Now!.

Popular Categories





Second highest salary in sql

+5 votes
349 views
asked in PHP Interview Question by anonymous

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
answered by duke 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
0 votes
answered by amit_pammu Expert (5,030 points)
edited by amit_pammu

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

+2 votes
1 answer 855 views
+8 votes
2 answers 104 views
+2 votes
1 answer 88 views
+12 votes
1 answer 108 views

Not a Member yet?

Ask to Folks Login

My Account
155 Folks are online
0 members and 155 guest online
Your feedback is highly appreciated