Query for second maximum salary in each in each department
Answers were Sorted based on User's Feedback
Answer / gopinath
select department_id, salary from
(select department_id, salary, rank() over(partition by
department_id order by salary desc) r from employees) where r=2;
cheers;
Is This Answer Correct ? | 35 Yes | 13 No |
Answer / ram
SELECT empno,salary,dept_id FROM (SELECT empno,salary,dept_id,DENSE_RANK() OVER (PARTITION BY Dept_id ORDER BY Salary DESC)
as Rnk FROM emp) WHERE Rnk = 2;
Is This Answer Correct ? | 8 Yes | 0 No |
Answer / manju
select deptno,Max(e1.sal) from emp e1
where e1.sal NOT IN(
select Max(e2.sal) from emp e2
group by e2.deptno
)
group by e1.deptno
Is This Answer Correct ? | 13 Yes | 10 No |
Answer / swastik
SELECT *
FROM
(
SELECT Ename, Deptno, Sal,
DENSE_RANK(PARTITION BY Deptno
ORDER BY Sal DESC
)TopRank
FROM Emp
)
WHERE TopRank = 2
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / swas
SELECT *
FROM
(
SELECT e1.*,
DENSE_RANK() OVER(PARTITION BY Deptno
ORDER BY Sal DESC
)TopRank
FROM Emp e1
)
WHERE TopRank = 2
/
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / akula
select * from emp e1 where 2 =(select count(distinct(sal)) from emp e2 where e1.dno=e2.dno and e1.sal<=e2.sal);
Is This Answer Correct ? | 1 Yes | 1 No |
Answer / swaraj
Select Department,Max(Salary)
From (Select E1.Department,E1.Salary
From Employee E1,(Select Department,Max(Salary) as Salary
From Employee group by Department) E2
Where E1.Department = E2.Department
And E1.Salary<E2.Salary) E
Group by Department
Is This Answer Correct ? | 0 Yes | 0 No |
select * from employees e where (select count(salary) from
employees where salary >=e.salary)=2;
Is This Answer Correct ? | 0 Yes | 1 No |
Answer / sureshramsing
select * from(select row_number() over(partition by deptno order by sal desc) rn,a.* from emp a) where rn=1;
Is This Answer Correct ? | 0 Yes | 1 No |
Answer / suneelkumar
select * from emp x where 2=(select count(distinct sal) from
emp y where x.sal<=y.sal) group by deptno;
Is This Answer Correct ? | 0 Yes | 2 No |
Why do we need cursor in pl sql?
Can a table have no primary key?
How do I write a cron which will run a sql query and mail the results to agroup?
What does t sql mean?
What are different types of refreshment techniques of materialised view
When is the update_statistics command used?
Can we call stored procedure in function?
What is rank dense_rank and partition in sql?
Do ddl statements need commit?
what is 'trigger' in sql? : Sql dba
what is explain plan?
What is magic table?