select top 3 sal from each dept?

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

Question Posted / nag.vamshi

17 Answers
26942 Views
IBM, TCS, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

select top 3 sal from each dept?..

Answer / avinesh gupta

select top 3 sal,depno from emp where deptno in
(
select deptno from emp group by deptno
)
order by sal desc

Is This Answer Correct ?

0 Yes

1 No

select top 3 sal from each dept?..

Answer / anil

select * from(select ename,deptno,sal from emp order by sal desc) where rownum<=3

Is This Answer Correct ?

0 Yes

1 No

select top 3 sal from each dept?..

Answer / sambu

select r.sal,r.rank,r.dept high from
(select deptno,sal,dense_rank() over (partition by deptno
order by sal desc) rank
from emp) r
where r.rank=3;

The above query gives the following result

DEPTNO SAL HIGHEST
------ ----- -------
10 1300 3
20 1100 3
30 1500 3

Is This Answer Correct ?

3 Yes

7 No

select top 3 sal from each dept?..

Answer / kishore

select * from (
select sal,rownum as num from (select sal from
emp order by sal desc))
where num <=3;

Is This Answer Correct ?

0 Yes

4 No

select top 3 sal from each dept?..

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

UNION

SELECT DEPT, MAX(SAL)
FROM EMP
WHERE SAL NOT IN
(SELECT MAX(SAL)
FROM EMP
WHERE SAL NOT IN (SELECT MAX(SAL) FROM EMP GROUP
BY DEPT)
GROUP BY DEPT

UNION

SELECT MAX(SAL) FROM EMP GROUP BY DEPT)
GROUP BY DEPT

Is This Answer Correct ?

0 Yes

6 No

select top 3 sal from each dept?..

Answer / sen

select *
(select sal from emp
order by 1 desc)
where rownum < 4

Is This Answer Correct ?

1 Yes

13 No

select top 3 sal from each dept?..

Answer / asksam

Easy method to find top 3 sal -

Select distinct top 3 (sal) from emp order by sal desc

Is This Answer Correct ?

10 Yes

27 No

Post New Answer