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 |
What is a trigger in pl/sql?
What SQL keyword must immediately follow the UNION ALL statement? 1. SELECT 2. INTO 3. ORDER 4. WHERE 5. JOIN
How do I view tables in mysql?
what are the advantages of using stored procedures? : Sql dba
What is sql profiling in oracle?
What is scalar and vector?
Write a query to get 2nd maximum salary in an employee table ?
69 Answers Accenture, BirlaSoft, Letse, Logica CMG, Qwest, Rheal Software, Saagam, Semantic Space, Tailor Solution, TCS, TinyERP,
In a distributed database system, can we execute two queries simultaneously?
Show how functions and procedures are called in a pl/sql block.
Does SQL*Plus contains pl/sql Engine?
What are the triggers associated with image items?
How will you distinguish a global variable with a local variable in pl/sql?