Re: update with from

On Monday, January 23, 2012 10:11:00 pm Sim Zacks wrote:
> On 01/23/2012 07:10 PM, Adrian Klaver wrote:
> > On Monday, January 23, 2012 7:32:35 am Sim Zacks wrote:
> >> On 01/23/2012 05:13 PM, Adrian Klaver wrote:
> >>
> >>
> >> 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
> >>
> >> )
> >
> > To clarify what I posted earlier, my suggestion was based on rewriting
> > the
> >
> > second query as:
> > select b.partid,b.deliverywks 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])
> >
> > order by b.partid,b.unitprice, b.deliverywks
> > limit 1
> >
> > I may be missing the intent of your original query, but I think the above
> > gets to the same result without the IN.
>
> My first query returns all rows of each part ordered such so that the
> row I want to actually update the table with is last. This query returns
> 12000 rows, for the 600 parts I want to update.
>
> My second query with the limit within the subselect gets 1 row per part.
> This returns 600 rows, 1 row for each part I want to update.
>
> Your suggestion would only return one row.

Oops. So per a previous suggestion:

select DISTINCT ON (b.partid) b.partid, b.deliverywks 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])
order by b.partid,b.unitprice, b.deliverywks

>
> See
> http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks#Select_first_n_rows_fro
> m_group for reference.

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com