Query for second maximum salary in each in each department

Categories >> Software >> Databases >> SQL PLSQL
Suggest New Category

Question Posted / kumar.rathan

15 Answers
35907 Views
DST Global Solutions, TCS, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

Query for second maximum salary in each in each department..

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

Query for second maximum salary in each in each department..

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

Query for second maximum salary in each in each department..

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

Query for second maximum salary in each in each department..

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

Query for second maximum salary in each in each department..

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

Query for second maximum salary in each in each department..

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

Query for second maximum salary in each in each department..

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

Query for second maximum salary in each in each department..

Answer / arvind kumar

select * from employees e where (select count(salary) from
employees where salary >=e.salary)=2;

Is This Answer Correct ?

0 Yes

1 No

Query for second maximum salary in each in each department..

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

Query for second maximum salary in each in each department..

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

Post New Answer