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 difference between sql function and stored procedure?
What are the advantages and disadvantages, compared to the standard SQL and SQL*plus ?
What is the starting oracle error number? What is meant by forward declaration in functions?
What is an intersect?
What are the components of a PL/SQL block ?
Can you join views in sql?
what are the differences between get and post methods in form submitting. Give the case where we can use get and we can use post methods? : Sql dba
What is sql architecture?
What is trigger in pl sql with examples?
What is set serveroutput on?
What is function and procedure in pl sql?
1. what is the exact use of hint in sql. 2. How we can avoid index scan in sql even though index is there in the table.