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 |
how to include numeric values in sql statements? : Sql dba
What is pls integer?
Is sql free?
What is left join example?
Does sql*plus also have a pl/sql engine?
discuss about myisam index statistics collection. : Sql dba
What are the various restrictions imposed on view in terms of dml?
How do I create an index in word?
Is id a reserved word in sql?
What is synonyms?
Write a sql query to convert all character to uppercase after hypen.
What is an invalid partition table?