Home » SQL & PL/SQL » SQL & PL/SQL » regarding query(7.1version)
|
|
| Re: regarding query(7.1version) [message #23664 is a reply to message #23656] |
Mon, 23 December 2002 11:45   |
Amit Chauhan
Messages: 74
Registered: July 1999
|
Member |
|
|
Hi,
This is certainly one question asked many times :)
Heres a simple solution, but am not sure will it work in Oracle 7.1 or not (Quite old version, upgrade :) :
select * from
(select *
from emp
order by sal desc) a
where rownum <= 3
/
Hope that helps
Thanks
Amit
|
|
|
|
| Re: regarding query(7.1version) [message #23675 is a reply to message #23656] |
Tue, 24 December 2002 19:50 |
 |
Barbara Boehmer
Messages: 9096
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The method that Amit provided is the fastest method for retrieving the first 3 highest paid employees in Oracle 8i and 9i. However, prior to Oracle 8i, that is in Oracle 8.0 and Oracle 7, it was not allowed to have an order by clause within a sub-query, therefore you cannot use that method.
Below I have provided some older, slower methods that worked in Oracle 8.0. I don't know if they will work in Oracle 7.1 or not. Please test them and lest us know. You didn't provide your table structure, so I used the old Oracle emp demo table, for the examples. I also included the newer, faster methods, that you can use after you upgrade.
I have listed 8.0 methods for obtaining the first 3 highest paid employees for the company and the first 3 highest paid employees by department. I have also included 8.0 methods for retrieving the employees who earn the first 3 highest salaries, regardless of how many there are. This provides different results in case of ties. For example, if you want the 3 highest paid employees, you get one who earns $5,000 and two who earn $3,000. However, if you want the employees who earn the three highest salaries, you get one who earns $5,000, two who earn $3,000 and one who earns $2,975.
SQL> -- prior to Oracle 8i:
SQL> -- top 3 highest paid employees for whole company:
SQL> CLEAR BREAKS
SQL> SELECT outer.deptno, outer.empno, outer.ename, outer.sal
2 FROM emp outer
3 WHERE 3 >=
4 (SELECT COUNT (*) + 1
5 FROM emp inner
6 WHERE inner.sal > outer.sal)
7 ORDER BY outer.sal DESC
8 /
DEPTNO EMPNO ENAME SAL
---------- ---------- ---------- ----------
10 7839 KING 5000
20 7788 SCOTT 3000
20 7902 FORD 3000
SQL>
SQL>
SQL> -- prior to Oracle 8i:
SQL> -- top 3 highest paid employees for each department:
SQL> BREAK ON deptno
SQL> SELECT outer.deptno, outer.empno, outer.ename, outer.sal
2 FROM emp outer
3 WHERE 3 >=
4 (SELECT COUNT (*) + 1
5 FROM emp inner
6 WHERE inner.sal > outer.sal
7 AND inner.deptno = outer.deptno)
8 ORDER BY outer.deptno, outer.sal DESC
9 /
DEPTNO EMPNO ENAME SAL
---------- ---------- ---------- ----------
10 7839 KING 5000
7782 CLARK 2450
7934 MILLER 1300
20 7788 SCOTT 3000
7902 FORD 3000
7566 JONES 2975
30 7698 BLAKE 2850
7499 ALLEN 1600
7844 TURNER 1500
SQL>
SQL>
SQL> -- prior to Oracle 8i:
SQL> -- employees earning top 3 salaries for whole company:
SQL> CLEAR BREAKS
SQL> SELECT outer.deptno, outer.empno, outer.ename, outer.sal
2 FROM emp outer
3 WHERE 3 >=
4 (SELECT COUNT (DISTINCT sal) + 1
5 FROM emp inner
6 WHERE inner.sal > outer.sal)
7 ORDER BY outer.sal DESC
8 /
DEPTNO EMPNO ENAME SAL
---------- ---------- ---------- ----------
10 7839 KING 5000
20 7788 SCOTT 3000
20 7902 FORD 3000
20 7566 JONES 2975
SQL>
SQL>
SQL> -- prior to Oracle 8i:
SQL> -- employees earning top 3 salaries for each department:
SQL> BREAK ON deptno
SQL> SELECT outer.deptno, outer.empno, outer.ename, outer.sal
2 FROM emp outer
3 WHERE 3 >=
4 (SELECT COUNT (DISTINCT sal) + 1
5 FROM emp inner
6 WHERE inner.sal > outer.sal
7 AND inner.deptno = outer.deptno)
8 ORDER BY outer.deptno, outer.sal DESC
9 /
DEPTNO EMPNO ENAME SAL
---------- ---------- ---------- ----------
10 7839 KING 5000
7782 CLARK 2450
7934 MILLER 1300
20 7788 SCOTT 3000
7902 FORD 3000
7566 JONES 2975
7876 ADAMS 1100
30 7698 BLAKE 2850
7499 ALLEN 1600
7844 TURNER 1500
SQL>
SQL>
SQL> -- since Oracle 8i:
SQL> -- top 3 highest paid employees for whole company:
SQL> CLEAR BREAKS
SQL> SELECT deptno, empno, ename, sal
2 FROM (SELECT deptno, empno, ename, sal
3 FROM emp
4 ORDER BY sal DESC)
5 WHERE ROWNUM <= 3
6 /
DEPTNO EMPNO ENAME SAL
---------- ---------- ---------- ----------
10 7839 KING 5000
20 7788 SCOTT 3000
20 7902 FORD 3000
SQL>
SQL>
SQL> -- since Oracle 8i (requires Enterprise Edition):
SQL> -- top 3 highest paid employees for each department:
SQL> BREAK ON deptno
SQL> SELECT deptno, empno, ename, sal
2 FROM (SELECT deptno, empno, ename, sal,
3 RANK () OVER (PARTITION BY deptno ORDER BY sal DESC) AS rk
4 FROM emp)
5 WHERE rk <= 3
6 /
DEPTNO EMPNO ENAME SAL
---------- ---------- ---------- ----------
10 7839 KING 5000
7782 CLARK 2450
7934 MILLER 1300
20 7788 SCOTT 3000
7902 FORD 3000
7566 JONES 2975
30 7698 BLAKE 2850
7499 ALLEN 1600
7844 TURNER 1500
SQL>
SQL>
SQL> -- since Oracle 8i:
SQL> -- employees earning top 3 salaries for whole company:
SQL> CLEAR BREAKS
SQL> SELECT deptno, empno, ename, sal
2 FROM emp
3 WHERE sal IN
4 (SELECT sal
5 FROM (SELECT DISTINCT sal
6 FROM emp
7 ORDER BY sal DESC)
8 WHERE ROWNUM <= 3)
9 ORDER BY sal DESC
10 /
DEPTNO EMPNO ENAME SAL
---------- ---------- ---------- ----------
10 7839 KING 5000
20 7902 FORD 3000
20 7788 SCOTT 3000
20 7566 JONES 2975
SQL>
SQL>
SQL> -- since Oracle 8i (requires Enterprise Edition):
SQL> -- employees earning top 3 salaries for each department:
SQL> BREAK ON deptno
SQL> SELECT deptno, empno, ename, sal
2 FROM (SELECT deptno, empno, ename, sal,
3 DENSE_RANK () OVER (PARTITION BY deptno ORDER BY sal DESC) AS rk
4 FROM emp)
5 WHERE rk <= 3
6 /
DEPTNO EMPNO ENAME SAL
---------- ---------- ---------- ----------
10 7839 KING 5000
7782 CLARK 2450
7934 MILLER 1300
20 7788 SCOTT 3000
7902 FORD 3000
7566 JONES 2975
7876 ADAMS 1100
30 7698 BLAKE 2850
7499 ALLEN 1600
7844 TURNER 1500
SQL>
|
|
|
|
Goto Forum:
Current Time: Tue May 21 03:52:35 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
