| Author |
extract Top Ten order by date
|
|
| ma.giorgi@gmail.com 2005-09-26, 7:23 am |
| Hello everybody!
I'm trying to extract the first ten or twenty records from a table
ordering by date
the sql command is:
1 SELECT *
2 FROM (SELECT *
3 FROM (
4 SELECT a.*
5 FROM intradoc.article a
6 ORDER BY a.article_date
7 )
8 WHERE rownum < 20
9 )
this simple query should work but I receive the following error on line
6:
10:51:30 ORA-00907: missing right parenthesis
why??!!
I have tried also other simple subquery and if I put an ORDER BY
instruction in the subquery I receive always this error just before the
ORDER BY instruction
strange!??
I hope in your help!
Marco
| |
| Mark D Powell 2005-09-26, 11:23 am |
| Marco, you only need one nested query in your example
1 select ename
2 from (select ename from emp order by ename)
3* where rownum = 1
UT1 > /
ENAME
----------
ADAMS
HTH -- Mark D Powell --
| |
| Ed Prochak 2005-09-26, 8:23 pm |
|
Mark D Powell wrote:
> Marco, you only need one nested query in your example
>
> 1 select ename
> 2 from (select ename from emp order by ename)
> 3* where rownum = 1
> UT1 > /
>
> ENAME
> ----------
> ADAMS
>
> HTH -- Mark D Powell --
Or no subquery at all if you do it right by selecting the rows in the
application level.
e.g. in sqlplus
set PAUSE ON
set PAGESIZE 25
SELECT a.*
FROM intradoc.article a
ORDER BY a.article_date ;
stop the query after the first screenful.
Do you really need to use the fake ROWNUM to control this? Use the
cursor properly.
Ed
(ROWNUM is an abomination and should be banned)
| |
| Barbara Boehmer 2005-09-26, 8:23 pm |
| 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@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@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.
|
|
|
|