Problem in retrieving top 5 emp [message #7924] |
Thu, 17 July 2003 00:53 |
Zia Khattak
Messages: 22 Registered: July 2003
|
Junior Member |
|
|
Hi.
I have problem in retrieving top 5 emplyees who have highest salries.
select empno, sal from emp
order by sal desc
this query will display all employees i need to display only 5 employees order by sal desc
plz help.
|
|
|
Re: Problem in retrieving top 5 emp [message #7926 is a reply to message #7924] |
Thu, 17 July 2003 01:37 |
sujit
Messages: 94 Registered: April 2002
|
Member |
|
|
SQL> select ename,sal from emp e where 5>=(select count(sal) from emp
2 where emp.sal>e.sal) order by sal desc;
ENAME SAL
---------- ----------
KING 5000
SCOTT 3000
FORD 3000
JONES 2975
BLAKE 2850
CLARK 2450
6 rows selected.
|
|
|
Re: Problem in retrieving top 5 emp [message #7927 is a reply to message #7924] |
Thu, 17 July 2003 01:39 |
|
Maaher
Messages: 7065 Registered: December 2001
|
Senior Member |
|
|
Using analytical functions (from 8.1.6 and up):SQL> select empno, ename , sal, rank
2 from ( select empno
3 , ename
4 , sal
5 , DENSE_RANK() over (order by sal desc) RANK
6 from emp
7 )
8 where RANK < 6
9 /
EMPNO ENAME SAL RANK
---------- ---------- ---------- ----------
7839 KING 5000 1
7788 SCOTT 3000 2
7902 FORD 3000 2
7566 JONES 2975 3
7698 BLAKE 2850 4
7782 CLARK 2450 5
6 rows selected.
SQL> select empno, ename , sal, rank
2 from ( select empno
3 , ename
4 , sal
5 , RANK() over (order by sal desc) RANK
6 from emp
7 )
8 where RANK < 6
9 /
EMPNO ENAME SAL RANK
---------- ---------- ---------- ----------
7839 KING 5000 1
7788 SCOTT 3000 2
7902 FORD 3000 2
7566 JONES 2975 4
7698 BLAKE 2850 5
SQL> HTH,
MHE
|
|
|
Re: Problem in retrieving top 5 emp [message #7929 is a reply to message #7924] |
Thu, 17 July 2003 01:47 |
Jake
Messages: 14 Registered: August 2000
|
Junior Member |
|
|
Try the following codes:
select * from (select empno, sal from emb order by sal desc) where rownum<=5
or
select /*+index(sal)*/ empno, sal from emp where rownum<=5 and rownum>=0 order by sal desc
the second one is faster but you need to create an index for the sal column
|
|
|
|
|
|
|
|
Re: Problem in retrieving top 5 emp [message #7984 is a reply to message #7933] |
Mon, 21 July 2003 04:03 |
sachin kumar gupta
Messages: 157 Registered: March 2003
|
Senior Member |
|
|
But same query on my system is running fine:
SQL> select * from (select empno, sal from emp order by sal desc) where rownum<=5;
EMPNO SAL
--------- ---------
7839 5000
7788 3000
7902 3000
7566 2975
7698 2850
Regards,
Sachin
|
|
|
|