This is a common question in Interview “find the second highest salary of Employee details table”. I hope most of my friends also have faced this question. Here I am explaining about two different methods-
• Using Row_number()
• Without using any SQL Function.
This is my Employeedetails table.
Method 1: Using Row_number()
Here I am showing third highest salary.
Method 2 – With no SQL Server Function
Another method: Suggested by Fazal – one of the regular blog reader of SQLINFO.
SELECT MAX(salary)
FROM employeedetails
WHERE salary NOT IN (SELECT TOP 1 salary
FROM employeedetails
ORDER BY salary DESC)
Fazal Vahora
13 years agoHi Varun,
We can also fetch 2nd Highest Salary from following query:
select max(salary) from Employeedetails
where salary not in (select top 1 salary from Employeedetails order by salary desc )
Thanks & Regards,
Fazal Vahora
Varun R
13 years agoHi Fazal,
Sure.. we can also get the result using your query.
Periyar
13 years agoPlease tell me the possible watys to get not just the nth max salary but also the details of the employee who is getting the n th highest salary. Thanks.
Suman
13 years agoGreat post!! by the way heere are couple of more ways to find second highest salary in SQL
Satish
13 years agoHI DIS IS SATISH..
FIND 2 ND HIGHEST SAL…
ANS:SELECT MAX(SAL) FROM EMP
WHERE
SAL<(SELECT MAX(SAL) FROM EMP.
Manoranjan Kumar Tiwari
13 years agoSELECT TOP 1 Salery FROM ( SELECT TOP 2
Salery FROM employee ORDER BY Salery DESC)
AS emp ORDER BY Salery ASC
Lalit Raghuvanshi
13 years agoI got the solution:
Multiple Sql server queries to get second,third,fourth or nth highest/maximum salary from employee table
http://www.webcodeexpert.com/2014/08/sql-server-query-to-get.html