hi,
i am trying to execute the following query but getting "ora-00907 missing right parenthesis" in the subquery and it highlights the "order" when error occurs. But i am not sure what the problem is here as i have correct number of parenthesises?

update patients set p_number =
(select p_code from customers where p_number is null and p_code like 'AL%' order by p_date desc)
where rownum <10

I have tried running this in a select statement which works perfectly:
select * from
(select p_code from customers where p_number is null and p_code like 'AL%' order by p_date desc)
where rownum <10

can someone help me out Please?

Thanks
Alica

Hi Alica,

Here are some working examples of top-n queries:

http://www.dba-oracle.com/oracle_news/orac..._top_n_rows.htm


SELECT
ENAME,
SAL
FROM
(SELECT ENAME, SAL
FROM EMP
ORDER BY SAL DESC)
WHERE
ROWNUM < 4;

If you are using Oracle 9i you can use the RANK function also.


select
*
from
(select empno,sal,rank()
over (order by sal desc ) rnk
from emp)
where rnk <= 5;

my understanding is that sub-selects are generally a 'bad' thing when dealing with large amounts of data because they cannot be indexed and optimized (resulting in a full table scan of the virtual table). IF this is the case, is there a 'better' way to perform a top-n query that doesn't use a sub-select?

Hi IEFBR14 (Love your handle!),

>> IF this is the case, is there a 'better' way to perform a top-n query that doesn't use a sub-select?

That's was very true up to 10g where it impoved somewhat, but I avoid them in favor of the WITH clause of GTT's:

http://www.dba-oracle.com/t_sql_rewrite_temporary_tables.htm

What are you trying to do? Update 9 first rows of patients?