SQL to find the 5 highest paid emp

[lalleima]

Hi
Everyone,
I'm bit confuse with this little problem.
Suppose I have a table of structure like
emp(emp_id,Name,salary)
How can I select the 5 highest paid employees with a single query?
And mind U, it is not Indexed or a ordered View

Thank U for anyone who have read this and I'm open to any kind of sugestion.

Nishi

 

[carp]

SELECT * FROM
(SELECT emp_id,Name,salary
FROM emp
ORDER BY salary DESC)
WHERE rownum < 6;

 

[jee]

Another option is to use either the RANK or DENSE_RANK analytical functions. The analytical functions were designed for use in data warehouse environments and can be a lot faster if you have a lot of data. If you are running 8i and you want to do this within PL/SQL, you will have to use dynamic SQL as the PL/SQL engine doesn't support this year - probably works OK in 9i without dynamic SQL. Here is the blurb from the SQL Ref manual:

Example
The following statement ranks the employees within each department based on their salary and commission. Identical salary values receive the same rank and cause nonconsecutive ranks. Compare this example with the example for DENSE_RANK.



SELECT deptno, ename, sal, comm,
RANK() OVER (PARTITION BY deptno ORDER BY sal DESC, comm) as rk
FROM emp;

DEPTNO ENAME SAL COMM RK
---------- ---------- ---------- ---------- ----------
10 KING 5000 1
10 CLARK 2450 2
10 MILLER 1300 3
20 SCOTT 3000 1
20 FORD 3000 1
20 JONES 2975 3
20 ADAMS 1100 4
20 SMITH 800 5
30 BLAKE 2850 1
30 ALLEN 1600 300 2
30 TURNER 1500 0 3
30 WARD 1250 500 4
30 MARTIN 1250 1400 5
30 JAMES 950 6


If you wanted only the top 5 in each deparment, you would just need to add a &quot;Where rk <= 5&quot; clause.

 

[nme]

How about:

SELECT e.emp_id, e.Name, e.salary
FROM emp e
WHERE 5 > (SELECT COUNT(DISTINCT salary)
FROM emp
WHERE salary > e.salary)