Have a Question?

If you have any question you can ask below or enter what you are looking for!



2nd Highest salary in a table

SQL Query to find second highest salary in employee table?

  pankaj

This is most favorable interview question. You can solve this question from different ways-

Query 1:

select MAX(Sal) from Employee WHERE Sal NOT IN (select MAX(Sal) from Employee );

Query 2:

WITH CTE AS (
SELECT  Ename, sal, 
ROW_NUMBER() OVER(ORDER BY sal DESC) as RN
FROM Employee
)
SELECT Ename, sal
FROM CTE
WHERE RN = 2

Query 3: If you want to get the Nth highest salary, you just need to mention N(1/2/3/4/..N) in place of 2.

select A.Ename, A.sal from Employee A
where 2 = (select count(*) from Employee B where B.EmpNo >= A.EmpNo)


Thanks,

  Devbrat Tripathi       22 Aug 2016       1       0     

Post Your Answers:

Please use the CODE HIGHLIGHT Button to format/highlight your codes if any