有一数据表，记录了每个文件的出版日期及版本号，如下，现在想通过查询，查询出每个文件的最新的版次号，不知应该如果写sql语句？谢谢
表记录：
files date rev
a 2010-01-01 R1
b 2010-01-01 R1
a 2014-01-01 R4
希望输出结果：
files date rev
a 2014-01-01 R4
b 2010-01-01 R1
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-07-09 17:16:18
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[T]
if object_id('[T]') is not null drop table [T]
go
create table [T]([files] varchar(1),[date] datetime,[rev] varchar(2))
insert [T]
select 'a','2010-01-01','R1' union all
select 'b','2010-01-01','R1' union all
select 'a','2014-01-01','R4'
--------------开始查询--------------------------
SELECT *
from t a
WHERE EXISTS (SELECT 1 FROM (
select [files],MAX([date])[date]
from [T]
GROUP BY [files]) b WHERE a.[files]=b.[files] AND a.[date]=b.[date])
----------------结果----------------------------
/*
files date rev
----- ----------------------- ----
b 2010-01-01 00:00:00.000 R1
a 2014-01-01 00:00:00.000 R4
*/
------解决方案--------------------
2005或者以上
select [files],[date],[rev]
from (
select row_number() over(partition by [files] order by [date] desc) id ,*
from [T]) a
where id=1