日期:2014-05-17 浏览次数:20433 次
CREATE TABLE #temp
(
Customer_ID INT ,
Handler INT ,
Num INT ,
Tran_Date DATETIME
)
INSERT #temp
( Customer_ID ,
Handler ,
Num ,
Tran_Date
)
SELECT 73840,35240009,1,'2006/1/17 21:03'union
SELECT 73840,31130002,1,'2006/4/9 13:22'union
SELECT 73840,35570018,1,'2006/5/7 18:46'union
SELECT 73840,38290005,3,'2011/7/21 17:27'union
SELECT 73840,37550028,4,'2012/8/18 17:36'union
SELECT 73840,99990814,4,'2013/8/7 20:08'
SELECT * FROM #temp
ORDER BY #temp.Customer_ID,#temp.Num,#temp.Tran_Date
select Customer_ID,Handler from
(select Customer_ID,Handler,
row_number() over(partition by Customer_ID order by Num desc,Tran_Date desc) 'rn'
from #temp) t
where t.rn=1
/*
Customer_ID Handler
----------- -----------
73840 99990814
(1 row(s) affected)
*/