Thanks for the question, Nelson.
Asked: November 20, 2015 - 6:07 pm UTC
Last updated: November 21, 2015 - 1:43 am UTC
Version: Oracle7 Server Release 7.3.4.5.0 - Production
Viewed 1000+ times
You Asked
Hi,
Is there a way to do a top- N query in Oracle7?
For example, the following query works in recent versions:
select *
from
( select *
from emp
order by sal desc )
where ROWNUM <= 5;
However in Oracle7 it gives the error:
ORA-00907: missing right parenthesis.
Thanks,
Nelson
and Connor said...
SQL> select e.empno, e.ename, e.sal, x.seq
2 from scott.emp e,
3 ( select e2.empno, count(*) seq
4 from scott.emp e1, scott.emp e2
5 where e1.sal >= e2.sal
6 group by e2.empno
7 ) x
8 where e.empno = x.empno
9 and x.seq <= 5
10 order by x.seq;
EMPNO ENAME SAL SEQ
---------- ---------- ---------- ----------
7839 KING 5000 1
7788 SCOTT 3000 3
7902 FORD 3000 3
7566 JONES 2975 4
7698 BLAKE 2850 5
5 rows selected.
You can see why analytics are so cool when you consider the alternative :-)
Is this answer out of date? If it is, please let us know via a Comment