select top 3 sal from each dept?
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
What are different types of joins ?
Can procedure in a package be overloaded?
Why do we need databases?
How will you make performance better for application including front end and back
How to load data with sql*loader?
What is crud sql?
what are the advantages of sql ? : Sql dba
What is normalization in sql?
What is package in pl sql?
How to come back in normal stage in Mutating Table if mutating table is locked or update data?
What is Temp Table and type of temp table?
What is record variable?