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 |
function can return value ,procedure also return value through out parameter then what is the difference?
i have doubt that any one tell ref cursor comes in sql pl/sql? pls clarify?
What are the basic techniques of indexing?
i need department wise top 2 employees salary.which logic i will use
How many triggers can be applied on a table?
What is pl sql in dbms?
How to change the order of columns in Oracle SQL Plus ?
What is referential integrity ?
How do you exit in sql?
when is the use of update_statistics command? : Sql dba
What is different between union and minus?
Can we create non-clustured index on a clustered index ?