求一SQL语句——取分组中时间最靠近当前时间值的行
表结构与数据：
UUIDXX_IDSTATUSUPDATE_TIME
1112011/6/4 15:23
2122011/10/4 11:22
3112010/12/12 13:23
4212011/6/4 16:23
5222011/6/11 14:01
6312011/9/11 14:01
7322011/6/4 15:23
8322011/8/4 15:23
9412011/11/4 16:23
10412011/12/11 14:01
要求：
按照XX_ID分组 然后取出每个分组中时间与当前时间最接近的一行数据
想要的结果：

UUIDXX_IDSTATUSUPDATE_TIME
3112010/12/12 13:23
5222011/6/11 14:01
6312011/9/11 14:01
10412011/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 code
select * from tb t where UPDATE_TIME=(select max(UPDATE_TIME) from tb where XX_ID =t.XX_ID）
[解决办法]
SQL code
create 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