Im familiar with top n queries. Im trying to tune an update and try to do it in one update statement instead of a query. Im basically trying to update only the top N fields.
Here is the cursor(I have rewritten it as an analytic function, but I really want it as a single update. any suggestions? I re-wrote the cursor as follows, which is much faster, but I want to get away from pl/sql select * from (select pk, date, dense_rank() over (partition by pk order by date desc) tab from mytable a) tab where tab = 1 here is the cursor: declare cursor c_update is select pk, last_day(date) monthend_date, max(date) max_date from mytable group by pk, last_day(date); row integer; l_date date; begin select last_day (add_months(sysdate, -1) ) into l_date from dual; row := 0; for update_rec in c_update loop if update_rec.max_date <= l_date then update mytable set monthend_date = update_rec.monthend_date where pk = update_rec.pk and date = update_rec.max_perfdate; end if; end loop; commit; end; / I tried re-writing it as follows: but i get errors on the order by. any other possibilities? update mytable a set monthend_date = (select last_day(date) from mytable b where a.pk = b.pk and rownum = 1) where (pk,date) in (select pk,date from mytable c where c.pk = a.pk and rownum = 1 order by date desc) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).