OraFAQ Forum: SQL & PL/SQL » to display first 5 highest paid employee

Home » SQL & PL/SQL » SQL & PL/SQL » to display first 5 highest paid employee

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

to display first 5 highest paid employee [message #41017]

Mon, 18 November 2002 06:05

Gurdeep Singh
Messages: 64
Registered: October 2002

Member

please tell me query how display first five highest paid employee
with regards
Todd

Report message to a moderator

Re: to display first 5 highest paid employee [message #41018 is a reply to message #41017]

Mon, 18 November 2002 06:48

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

SQL> ed
Wrote file afiedt.buf

  1  SELECT *
  2          FROM   (SELECT * FROM emp ORDER BY sal DESC)
  3*         WHERE  ROWNUM < 6
SQL> /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

Report message to a moderator

Re: to display first 5 highest paid employee [message #41020 is a reply to message #41017]

Mon, 18 November 2002 07:53

Rick Cale
Messages: 111
Registered: February 2002

Senior Member

I think the user wanted the FIRST 5 highest paid employees. I have not tested the below query.

SELECT *
FROM (SELECT *
FROM emp
ORDER BY sal DESC,hiredate)
WHERE ROWNUM < 6

Report message to a moderator

Re: to display first 5 highest paid employee [message #41025 is a reply to message #41017]

Mon, 18 November 2002 21:50

Vikas Gupta
Messages: 115
Registered: February 2002

Senior Member

This is a variation and can give a answer taking into consideration if 2 employees are getting the same salary.

select * from emp a
where 5 >= (select count(*) from emp b where b.sal > a.sal);

Ans:

EMPNO ENAME SAL
--------- ---------- ---------
7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7902 FORD 3000

Report message to a moderator

Previous Topic:	Execute SQL*Loader from Stored Proc
Next Topic:	Rebuilding indexes

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 29 05:56:32 CDT 2024