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
32011 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 / nitin tomer

Query without using analytic function:

SELECT dept_id, MAX(salary)
FROM EMPLOYEE_DEPT WHERE rowid NOT IN (SELECT MAX(rowid) FROM EMPLOYEE_DEPT GROUP
BY dept_id )
GROUP BY dept_id
UNION
SELECT dept_id, MAX(salary)
FROM EMPLOYEE_DEPT
GROUP BY dept_id;

using row_number() function:

SELECT NAME,DEPT_ID,SALARY,RNM FROM
(SELECT NAME,DEPT_ID,SALARY,ROW_NUMBER()OVER(PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS RNM
FROM EMPLOYEE_DEPT)WHERE RNM<3;

Is This Answer Correct ?

0 Yes

0 No

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

Answer / manish gupta

select deptno,sal from (select * from emp order by sal
desc) where rownum<3
union
select deptno,sal from (select * from emp order by sal
desc) where rownum<3 and deptno not in(20);

considering scott table in db.

Is This Answer Correct ?

0 Yes

1 No

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

Answer / priyank shah

SELECT * FROM (SELECT ENAME,SAL FROM EMP ORDER BY SAL
DESC)
WHERE ROWNUM < 3

Is This Answer Correct ?

1 Yes

2 No

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

Answer / arun

Select top(2)* from table order by salary desc

Is This Answer Correct ?

2 Yes

7 No

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

Answer / mukesh kumar

SELECT * FROM (SELECT NAME,SALARY FROM EMP ORDER BY SALARY
DESC)
WHERE ROWNUM < 3

Is This Answer Correct ?

1 Yes

7 No

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

Answer / ramya p

select deptno, max(sal) from (select * from emp order by
sal desc)
where rownum < 3
group by deptno
order by max(sal) desc;

Is This Answer Correct ?

5 Yes

12 No

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

Answer / ramya p

Select * from emp where sal in
(Select * From (Select sal from emp order by sal desc)
Where rownum < 3) order by sal desc;

Is This Answer Correct ?

6 Yes

18 No

Post New Answer