sql question


po_mst ( po_no, po_date, shop_no, can_flag, status)
po_dtl ( po_no, item_no, price, can_flag, status)

I need to find the most recent price for each item in each shop.

select pd.price
from po_mst pm, po_dtl pd, (
select mm.shop_no, dd.item_no, max(mm.po_date) as mdate
from po_mst mm, po_dtl dd
where mm.po_no=dd.po_no
and mm.status<>'V' and mm.can_flag=0
and dd.status<>'V' and dd.can_flag=0
group by mm.shop_no, dd.item_no
) ee
where pm.po_no=pd.po_no
and pm.status<>'V' and pm.can_flag=0
and pm.status<>'V' and pm.can_flag=0
and pm.po_date=ee.mdate
and pm.shop_no=ee.shop_no
and pd.item_no=ee.item_no

I think it's not the best solution so would like some advices.

--
@~@ Might, Courage, Vision, SINCERITY.
/ v \ Simplicity is Beauty! May the Force and Farce be with you!
/( _ )\ (Xubuntu 7.04) Linux 2.6.22.1
^ ^ 13:19:01 up 2 days 15:23 0 users load average: 0.01 0.01 0.00
news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk
.

Relevant Pages

  • Re: Campagnolo parts (Shimagnolo in the making) - part prices? Best course of action?
    ... *Campagnolo Centaur Rear Hub*, told that the only difference between ... Veloce or anything better quality? ... Campy chain...but the price for a chain seems REALLY, ... bike shop will be able to buy a part and make what is, in all likelihood, a very good margin. ...
    (rec.bicycles.tech)
  • Re: New Shimano discount parts
    ... based place with 4 very experienced wrenchs that don't come cheap(80+ ... just keepin' everybody happy-type shop. ... maintainence and repair shop. ... hot price comes along. ...
    (rec.bicycles.tech)
  • Re: Silver
    ... bullion market, particularly for small to intermediate sized demand. ... As the price falls, the street demand is soaring and all of a sudden ... One large shop is asking ... and Spot +$3.00 for Silver Eagles. ...
    (rec.collecting.coins)
  • Chingwa
    ... "Taskforce" has been unleashed by the government to force shop owners and ... The price cut enforcers are army men ... The result of it all, inevitably, is rapid collapse and many goods and foods ... stretched his hands up to the policeman and said: ...
    (soc.culture.zimbabwe)
  • Re: The local guys gets the sale...
    ... I stopped by one of the local welding supply shops today to price compare ... I looked at a Miller 212 with a spool gun. ... Selling a welder package you don't even have in stock doesn't ... Also, it's sometimes helpful to loose a few bucks to shop local, ...
    (sci.engr.joining.welding)
Loading