i need department wise top 2 employees salary.which logic i will use

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

i need department wise top 2 employees salary.which logic
i will use

Question Posted / siri

17 Answers
27868 Views
I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

i need department wise top 2 employees salary.which logic i will use..

Answer / neetika vardhan

SELECT dept, MAX(sal)
FROM EMP WHERE sal NOT IN (SELECT MAX(sal) FROM emp GROUP
BY dept)
GROUP BY dept

UNION

SELECT dept, MAX(sal)
FROM EMP
GROUP BY dept

Is This Answer Correct ?

17 Yes

8 No

i need department wise top 2 employees salary.which logic i will use..

Answer / kavitha

SELECT distinct a.department_id, a.salary
FROM (SELECT department_id, salary,
DENSE_RANK () OVER (PARTITION BY department_id ORDER BY salary DESC NULLS LAST) max_sal
FROM employees
WHERE department_id IS NOT NULL) a
WHERE max_sal <= 2

Is This Answer Correct ?

5 Yes

1 No

i need department wise top 2 employees salary.which logic i will use..

Answer / nag

select distinct(sal), deptno from
(select * from emp a where 1 = (select count(distinct sal)+1
from emp b where b.sal > a.sal and b.deptno = a.deptno) and
deptno is not null
union
select * from emp a where 2 = (select count(distinct sal)+1
from emp b where b.sal > a.sal and b.deptno = a.deptno)and
deptno is not null)
order by deptno,sal

Is This Answer Correct ?

3 Yes

0 No

i need department wise top 2 employees salary.which logic i will use..

Answer / naresh anumolu

SELECT * FROM(SELECT *,DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) AS RANK FROM EMP)A WHERE RANK<3

Is This Answer Correct ?

2 Yes

0 No

i need department wise top 2 employees salary.which logic i will use..

Answer / shreya

select level,deptno,max(sal)
from emp
where level in (1,2)
connect byy prior sal>sal
group by level,deptno
order by deptno

Is This Answer Correct ?

2 Yes

0 No

i need department wise top 2 employees salary.which logic i will use..

Answer / neo

select t1.empno, deptno, sal from emp t1 where t1.sal =
(select max(t2.sal) from emp t2 where t1.deptno = t2.deptno
group by deptno)
union
select t1.empno, deptno, sal from emp t1 where t1.sal =
(select max(t2.sal) from emp t2
where t2.sal < (select max(t3.sal) from emp t3 where
t3.deptno = t2.deptno and t1.deptno = t2.deptno )
group by deptno)
order by deptno, sal desc;

Is This Answer Correct ?

1 Yes

0 No

i need department wise top 2 employees salary.which logic i will use..

Answer / vikas kapoor

select deptno,sal from
(select deptno,sal,row_number() over(partition by deptno orderby sal) r from emp ) where r<3

Is This Answer Correct ?

1 Yes

0 No

i need department wise top 2 employees salary.which logic i will use..

Answer / ethayan

select foracid,clr_bal_amt from (select
foracid,clr_bal_amt,rank() over (order by clr_bal_amt desc
nulls last) as ranking
from tbaadm.gam order by a,clr_bal_amt nulls last)
where ranking=2

Is This Answer Correct ?

1 Yes

1 No

i need department wise top 2 employees salary.which logic i will use..

Answer / ashok kumar y

SELECT a.deptno, a.sal
FROM (SELECT deptno, sal,
DENSE_RANK () OVER (PARTITION BY deptno ORDER BY sal DESC NULLS LAST) max_sal
FROM emp
WHERE deptno IS NOT NULL) a
WHERE max_sal <= 2 group by deptno,sal ;

Is This Answer Correct ?

0 Yes

0 No

i need department wise top 2 employees salary.which logic i will use..

Answer / shyam

select * from
(select ename,job,sal,deptno,
dense_rank()
over(partition by deptno order by sal desc) densrank
from emp)
where densrank<=2;

Is This Answer Correct ?

0 Yes

0 No

Post New Answer