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