select top 3 sal from each dept?

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

Question Posted / nag.vamshi

17 Answers
23905 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 / nag.vamshi

select* from(select ename,deptno,sal,row_number()
over(partiton by deptno order by sal)num from emp)
where num<=3
order by deptno;

Is This Answer Correct ?

24 Yes

11 No

select top 3 sal from each dept?..

Answer / santo

SELECT r.salary, r.department_id, r.RANK highest
FROM (SELECT salary, department_id,
DENSE_RANK () OVER (PARTITION BY
department_id ORDER BY salary DESC)

RANK
FROM employees) r
WHERE r.RANK <= 3
ORDER BY 2

Is This Answer Correct ?

10 Yes

0 No

select top 3 sal from each dept?..

Answer / venkateswara thiruvedhula

SELECT empno,
deptno,
sal,
RANK() OVER (PARTITION BY deptno ORDER BY sal
desc) "rank"
FROM emp;

EMPNO DEPTNO SAL rank
---------- ---------- ---------- ----------
7934 10 5000 1
7782 10 3000 2
7839 10 2000 3
7369 20 2950 1
7876 20 1700 2
7566 20 1100 3
7788 20 900 4
7902 20 900 4

Is This Answer Correct ?

2 Yes

1 No

select top 3 sal from each dept?..

Answer / shailesh j

SELECT * FROM (
SELECT DEPARTMENT_ID,SALARY,DENSE_RANK() OVER(PARTITION BY
DEPARTMENT_ID ORDER BY SALARY DESC) AS RK FROM EMPLOYEES)
WHERE RK<4;

Is This Answer Correct ?

1 Yes

0 No

select top 3 sal from each dept?..

Answer / ramireddy

select * from emp e where &n=(select count(distinct Sal)from
emp where Sal<=e.Sal);

Is This Answer Correct ?

2 Yes

2 No

select top 3 sal from each dept?..

Answer / sasi

select * from
(select sal,rank() over (order by sal desc) emprank from emp)
where emprank<=3

Is This Answer Correct ?

1 Yes

1 No

select top 3 sal from each dept?..

Answer / keshav

with Q as (select *,dense_rank() over(partition by dept
order by sal) as dr from dept
select * from Q
where dr=3

Is This Answer Correct ?

0 Yes

0 No

select top 3 sal from each dept?..

Answer / chiru

select *
from (select row_number() over(partition by deptno order by sal desc) r,
b.*
from (select rownum num, a.* from emp a order by sal desc) b)
where r <= 3

Is This Answer Correct ?

0 Yes

0 No

select top 3 sal from each dept?..

Answer / divakarreddy

SQL> select *from (select ename,sal,deptno,rank()
2 over(partition by deptno order by sal desc)topsal
3 from emp)
4 where topsal<=3
5 order by deptno,sal desc;

ENAME SAL DEPTNO TOPSAL
---------- ---------- ---------- ----------
KING 5000 10 1
MILLER 3000 10 2
CLARK 2450 10 3
SCOTT 3000 20 1
JONES 2975 20 2
ADAMS 1100 20 3
BLAKE 2850 30 1
ALLEN 1600 30 2
TURNER 1500 30 3

9 rows selected.

Is This Answer Correct ?

0 Yes

0 No

select top 3 sal from each dept?..

Answer / ashwini

Select * from emp A where 2>=(select count(distinct(B.sal)) from emp B where A.sal<B.sal);

Is This Answer Correct ?

0 Yes

0 No

Post New Answer