sql question
- From: Man-wai Chang <toylet.toylet@xxxxxxxxx>
- Date: Sat, 14 Jul 2007 13:20:39 +0800
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
.
- Follow-Ups:
- Re: thank you all
- From: Man-wai Chang
- Re: sql question
- From: Anders Altberg
- Re: sql question
- From: Man-wai Chang
- Re: sql question
- From: Anders Altberg
- Re: sql question
- From: Anders Altberg
- Re: thank you all
- Prev by Date: Re: Modify file xx in desktop
- Next by Date: Re: case insensitive order by...
- Previous by thread: Modify file xx in desktop
- Next by thread: Re: sql question
- Index(es):
Relevant Pages
|
Loading