Home » SQL & PL/SQL » SQL & PL/SQL » Problem in retrieving top 5 emp
Problem in retrieving top 5 emp [message #7924] Thu, 17 July 2003 00:53 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #7932 is a reply to message #7924] Thu, 17 July 2003 02:42 Go to previous messageGo to next message
sachin kumar gupta
Messages: 157
Registered: March 2003
Senior Member
This will work:

select * from (select empno, sal from emp order by sal desc) where rownum<=5;

Regards,
Sachin
Re: Problem in retrieving top 5 emp [message #7933 is a reply to message #7932] Thu, 17 July 2003 05:50 Go to previous messageGo to next message
Zia Khattak
Messages: 22
Registered: July 2003
Junior Member
Bundle of thanks for u my dear but this query.
is producing error like " missing right parenthesis "

select * from (select empno, sal from emp order by sal desc) where rownum<=5;
Thanks Jake [message #7934 is a reply to message #7929] Thu, 17 July 2003 06:35 Go to previous messageGo to next message
Zia Khattak
Messages: 22
Registered: July 2003
Junior Member
Thanks a lot Jake
Thanks Maaher [message #7935 is a reply to message #7927] Thu, 17 July 2003 06:39 Go to previous messageGo to next message
Zia Khattak
Messages: 22
Registered: July 2003
Junior Member
Thanks a lot Maaher.
Thanks Sujit [message #7936 is a reply to message #7926] Thu, 17 July 2003 06:42 Go to previous messageGo to next message
Zia Khattak
Messages: 22
Registered: July 2003
Junior Member
Thanks a lot dear,
you realy solve my problem.

regards,
zia.
Re: Problem in retrieving top 5 emp [message #7984 is a reply to message #7933] Mon, 21 July 2003 04:03 Go to previous messageGo to next message
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
Re: Problem in retrieving top 5 emp [message #8002 is a reply to message #7984] Mon, 21 July 2003 23:32 Go to previous message
Zia Khattak
Messages: 22
Registered: July 2003
Junior Member
I think we have different version of oracle thats y this query is producing error i have oracle 8.0 by the way thanks.
Previous Topic: Copying of data files from 1 machine to another
Next Topic: QUERY...
Goto Forum:
  


Current Time: Fri Mar 29 02:02:03 CDT 2024