Re: extract Top Ten order by date

From: Barbara Boehmer <baboehme_at_hotmail.com>
Date: 26 Sep 2005 14:05:50 -0700
Message-ID: <1127768750.047525.159680@g44g2000cwa.googlegroups.com>

You must be using a very old, outdated version of Oracle, at least 8.0 or earlier, when an order by clause was not allowed in a subquery. That sort of query works fine in currently supported versions, as shown below:

scott_at_ORA92> SELECT *
  2 FROM (SELECT a.*

  3  	     FROM   scott.emp a
  4  	     ORDER  BY a.hiredate)

  5 WHERE ROWNUM <= 5
  6 ORDER BY hiredate
  7 /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL
COMM     DEPTNO

---------- ---------- --------- ---------- --------- ----------

• ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

In 8.0 you have to use something like this, which is less efficient:

scott_at_ORA92> SELECT *
  2 FROM scott.emp b
  3 WHERE 5 >=

  4  	    (SELECT COUNT (*)
  5  	     FROM   scott.emp a
  6  	     WHERE  a.hiredate <= b.hiredate)

  7 ORDER BY hiredate
  8 /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL
COMM     DEPTNO

---------- ---------- --------- ---------- --------- ----------

• ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

You should upgrade your database to a currently supported version, at least 9i. Received on Mon Sep 26 2005 - 16:05:50 CDT