## Microsoft Interview Question

Software Engineer / DevelopersMy Previous answer will only return second highest record... More general solution is...

select a.sal from salary a where "N" = (select count(distinct b.sal) from salary b where a.sal <= b.sal)

Change N to 2 to get second highest... change it to 3 to get 3rd highest and so on.

select max(salary) from employee_salary where salary<(select max(salary)from employee_salary)

this query only selects second highest salary...the questions asks for both second highest salary and the corresponding Employee Id

Without Analytical functions

{

select empno,ename,sal from emp

where sal=(select max(sal) from emp where sal<(select max(sal) from emp));

}

Efficient way using analytical function

{

select empno,ename,sal from

(select empno,ename,sal, dense_rank() over (order by sal desc) r from emp)

where r=2;

}

SELECT salary, id FROM employee_salary WHERE salary=(SELECT MAX(salary) FROM employee_salary WHERE salary < (SELECT MAX(salary) FROM employee_salary))

- Anonymous December 01, 2009