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 |
How many types of database triggers can be specified on a table ? What are they ?
What is mutating sql table?
How do you update a table in sql?
How do you write a subquery?
what are the differences among rownum, rank and dense_rank? : Sql dba
Does indexing improve query performance?
Can we insert in view in sql?
How many columns should be in an index?
What are predefined functions in sql?
What is the use of function "module procedure" in pl/sql?
What is a left inner join?
Initially question was asked to mention the types of indexes. Then asked about BITMAP INDEX and B-Tree Index