Help - Search - Members - Calendar
Full Version: top-'n' query
Oracle DBA Forums > Oracle > Oracle Forum
Alicia
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
burleson
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;
iefbr14
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?
burleson
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
Laurent Schneider
QUOTE (Alicia @ Jun 20 2007, 01:16 AM) *
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



What are you trying to do? Update 9 first rows of patients?
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.