This is most favorable interview question. You can solve this question from different ways-
select MAX(Sal) from Employee WHERE Sal NOT IN (select MAX(Sal) from Employee );
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