求一SQL语句——取分组中时间最靠近当前时间值的行解决办法_Sql Server

求一SQL语句——取分组中时间最靠近当前时间值的行
表结构与数据：
UUID XX_ID STATUS UPDATE_TIME
1 1 1 2011/6/4 15:23
2 1 2 2011/10/4 11:22
3 1 1 2010/12/12 13:23
4 2 1 2011/6/4 16:23
5 2 2 2011/6/11 14:01
6 3 1 2011/9/11 14:01
7 3 2 2011/6/4 15:23
8 3 2 2011/8/4 15:23
9 4 1 2011/11/4 16:23
10 4 1 2011/12/11 14:01
要求：
按照XX_ID分组然后取出每个分组中时间与当前时间最接近的一行数据
想要的结果：

UUID XX_ID STATUS UPDATE_TIME
3 1 1 2010/12/12 13:23
5 2 2 2011/6/11 14:01
6 3 1 2011/9/11 14:01
10 4 1 2011/12/11 14:01

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

SQL code

--> 测试数据：#if object_id('tempdb.dbo.#') is not null drop table #create table #(UUID int, XX_ID int, STATUS int, UPDATE_TIME datetime)insert into #select 1, 1, 1, '2011/6/4 15:23' union allselect 2, 1, 2, '2011/10/4 11:22' union allselect 3, 1, 1, '2010/12/12 13:23' union allselect 4, 2, 1, '2011/6/4 16:23' union allselect 5, 2, 2, '2011/6/11 14:01' union allselect 6, 3, 1, '2011/9/11 14:01' union allselect 7, 3, 2, '2011/6/4 15:23' union allselect 8, 3, 2, '2011/8/4 15:23' union allselect 9, 4, 1, '2011/11/4 16:23' union allselect 10, 4, 1, '2011/12/11 14:01'select * from # t where UPDATE_TIME = (select top 1 UPDATE_TIME from # where XX_ID=t.XX_ID order by abs(datediff(second,getdate(),UPDATE_TIME)))/*UUID        XX_ID       STATUS      UPDATE_TIME----------- ----------- ----------- -----------------------2           1           2           2011-10-04 11:22:00.0005           2           2           2011-06-11 14:01:00.0006           3           1           2011-09-11 14:01:00.00010          4           1           2011-12-11 14:01:00.000*/
------解决方案--------------------
SQL codeselect * from tb t where UPDATE_TIME=(select max(UPDATE_TIME) from tb where XX_ID =t.XX_ID）
------解决方案--------------------
SQL codecreate index indexname on tb(xx_id,UPDATE_TIME desc)goselect *from(select *,       rn=row_number()over(partition by XX_ID order by UPDATE_TIME desc)from tb)twhere rn=1