On 01/23/2012 05:13 PM, Adrian Klaver wrote: > > I guess the primary question here is, what are you trying to achieve? > Do want a particular row to supply the values to the target table i.e the row > with the most timestamp? > What is the query you are using? > The query returns a partid, unitprice and delivery weeks from the latest set of rfqs sent. I want to update the table with the delivery weeks per part of the cheapest of those rfqs. This is the update stmt I am using, assuming that it always updates the table with the last row per part: update stat_allocated_components a set partarrivedate=current_date+(b.deliverywks*7),partarrivedate_source='RFQ Est' from (select b.popartid,b.partid,b.unitprice,b.deliverywks from poparts b join pos c using(poid) join lastrfqdateperpart d using(partid) where c.isrfq and c.issuedate > d.issuedate-7 AND b.unitprice > 0::numeric AND b.quantity >= 100::numeric AND c.postatusid = ANY (ARRAY[40, 41]) order by b.partid,b.unitprice desc, b.deliverywks desc) b where a.partid=b.partid and partarrivedate is null and a.stock-a.previouscommitmentlf+a.quantity<0 and b.deliverywks is not null This query take 163 ms. When I throw in code to make the select only return the correct rows The select statement takes 9 secs by itself: select a.partid,a.deliverywks from poparts a where popartid in ( select b.popartid from poparts b join pos c using(poid) join stock.lastrfqdateperpart d using(partid) where c.isrfq and c.issuedate > d.issuedate-7 AND b.unitprice > 0::numeric AND b.quantity >= 100::numeric AND c.postatusid = ANY (ARRAY[40, 41]) and b.partid=a.partid order by b.partid,b.unitprice, b.deliverywks limit 1 ) -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
