有一个表结构如下:
PRODUCT VENDORCODE EFF_DTE PRC CUR
110075311 A00000 20031012 0.25 USD
110075311 A00000 20041112 0.27 USD
110075311 C00000 20051112 0.31 USD
110076011 A00000 20031204 0.21 USD
110076011 A00000 20051204 0.22 USD
110076011 B00000 20081204 0.23 USD
110076011 B00000 20071204 0.24 USD
110076011 C00000 20091204 0.25 USD现在我想求得的结果是:每个PRODUCT的不同供应商的最后一个EFF_DTE的价格和币种结果和格式如下:
PRODUCT VENDORCODE EFF_DTE PRC CUR
110075311 A00000 20041112 0.27 USD
110075311 C00000 20051112 0.31 USD
110076011 A00000 20051204 0.22 USD
110076011 B00000 20081204 0.23 USD
110076011 C00000 20091204 0.25 USD这个语句应该如何写呢?
PRODUCT VENDORCODE EFF_DTE PRC CUR
110075311 A00000 20031012 0.25 USD
110075311 A00000 20041112 0.27 USD
110075311 C00000 20051112 0.31 USD
110076011 A00000 20031204 0.21 USD
110076011 A00000 20051204 0.22 USD
110076011 B00000 20081204 0.23 USD
110076011 B00000 20071204 0.24 USD
110076011 C00000 20091204 0.25 USD现在我想求得的结果是:每个PRODUCT的不同供应商的最后一个EFF_DTE的价格和币种结果和格式如下:
PRODUCT VENDORCODE EFF_DTE PRC CUR
110075311 A00000 20041112 0.27 USD
110075311 C00000 20051112 0.31 USD
110076011 A00000 20051204 0.22 USD
110076011 B00000 20081204 0.23 USD
110076011 C00000 20091204 0.25 USD这个语句应该如何写呢?
table a group by product,vendorcode
不对呀,你取的PRC是最后日期的那个prc吗?
select PRODUCT,VENDORCODE,EFF_DTE,PRC,CUR from
(select t.*,row_number()over(partition by product,VENDORCODE order by eff_dte desc)rn
from table1 t)
where rn=1