请问一个查询表中某物料最新采购价格信息的SQL如何写

请教一个查询表中某物料最新采购价格信息的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
A138009.00032010-04-10 00:00:000.002200RMB01货到付款
A138009.00052010-04-10 00:00:000.002200RMB01货到付款
A141009.00052011-06-23 00:00:000.000280USD01NULL
A141009.00062011-06-23 00:00:000.000280USD01NULL
A138009.00062010-04-10 00:00:000.002200RMB01货到付款
A138009.00082010-04-10 00:00:000.002200RMB01货到付款
A141009.00082011-06-23 00:00:000.000280USD01NULL
A138009.00092010-04-10 00:00:000.002200RMB01货到付款
A138009.00112010-04-10 00:00:000.002200RMB01货到付款
A141009.00112011-06-23 00:00:000.000280USD01NULL
A141009.00122011-06-23 00:00:000.000280USD01NULL
A141009.00132011-06-23 00:00:000.000280USD01NULL
A138009.00132010-04-10 00:00:000.002200RMB01货到付款
B057009.00132008-12-04 00:00:000.002400RMB01货到付款
A138009.00142010-04-10 00:00:000.002200RMB01货到付款
A141009.00142011-07-26 00:00:000.000280USD01NULL
A141009.00152011-06-23 00:00:000.000280USD01NULL
A138009.00152010-04-10 00:00:000.002200RMB01货到付款

[解决办法]

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