请教一个查询表中某物料最新采购价格信息的SQL怎么写？
表名：vendor_price
表中字段：vendor_id,part_id,effec_date,unit_price,currency_id,unit,price_term ......
vendor_id:供应商代码
part_id:物料代码
effec_date:采购价起效日期（关键）
unit_price:采购价格
currency_id:结算货币
unit:单位
price_term:结算方式

需求：按part_id查询effec_date时间最新的对应的unit_price信息,不用考虑供应商
vendor_id part_id effec_date unit_price currency_id unit price_term
A138 009.0003 2010-04-10 00:00:00 0.002200 RMB 01 货到付款
A138 009.0005 2010-04-10 00:00:00 0.002200 RMB 01 货到付款
A141 009.0005 2011-06-23 00:00:00 0.000280 USD 01 NULL
A141 009.0006 2011-06-23 00:00:00 0.000280 USD 01 NULL
A138 009.0006 2010-04-10 00:00:00 0.002200 RMB 01 货到付款
A138 009.0008 2010-04-10 00:00:00 0.002200 RMB 01 货到付款
A141 009.0008 2011-06-23 00:00:00 0.000280 USD 01 NULL
A138 009.0009 2010-04-10 00:00:00 0.002200 RMB 01 货到付款
A138 009.0011 2010-04-10 00:00:00 0.002200 RMB 01 货到付款
A141 009.0011 2011-06-23 00:00:00 0.000280 USD 01 NULL
A141 009.0012 2011-06-23 00:00:00 0.000280 USD 01 NULL
A141 009.0013 2011-06-23 00:00:00 0.000280 USD 01 NULL
A138 009.0013 2010-04-10 00:00:00 0.002200 RMB 01 货到付款
B057 009.0013 2008-12-04 00:00:00 0.002400 RMB 01 货到付款
A138 009.0014 2010-04-10 00:00:00 0.002200 RMB 01 货到付款
A141 009.0014 2011-07-26 00:00:00 0.000280 USD 01 NULL
A141 009.0015 2011-06-23 00:00:00 0.000280 USD 01 NULL
A138 009.0015 2010-04-10 00:00:00 0.002200 RMB 01 货到付款

------解决方案--------------------

SQL code

;
WITH    tmp
          AS ( SELECT   * ,
                        rn = row_number() OVER ( PARTITION BY part_id ORDER BY effec_date DESC )
               FROM     vendor_price
             )
    SELECT  *
    FROM    tmp
    WHERE   rn = 1