SQL Server 求記錄中的最新資料的方法

/*已知資料:

Column1   Column2     Column3    Column4
A         10           am         1999-01-01 00:00:00.000
A         11           am         1999-01-02 00:00:00.000                    
B         12           bm         1999-01-03 00:00:00.000                    
B         13           bm         1999-01-04 00:00:00.000                   
C         14           cm         1999-01-05 00:00:00.000                    
C         15           cm         1999-01-06 00:00:00.000

要求得到資料:

Column1  Column2     Column3     Column4                  
A        11             am      1999-01-02 00:00:00.000                    
B        13             bm      1999-01-04 00:00:00.000                    
C        15             cm      1999-01-06 00:00:00.000 */

--資料裝載

Create Table #T(Column1 varchar(10),Column2 int,Column3 varchar(10),Column4 datetime)
insert #T select 'A',10,'am','1999-1-1'
union all select 'A',11,'am','1999-1-2'
union all select 'B',12,'bm','1999-1-3'
union all select 'B',13,'bm','1999-1-4'
union all select 'C',14,'cm','1999-1-5'
union all select 'C',15,'cm','1999-1-6'

--測試語句 方法1：

select a.* from #T a
where (a.Column4) = (select top 1 (Column4) from #T where Column1 = a.Column1 order by Column4 desc)

--測試結果:

Column1    Column2     Column3    Column4
---------- ----------- ---------- ------------------------------------------------------
A          11          am         1999-01-02 00:00:00.000
B          13          bm         1999-01-04 00:00:00.000
C          15          cm         1999-01-06 00:00:00.000

--測試語句 方法2:(最佳效率)

select a.* from #T a join( select Column1,Column4=max(Column4)
from #T group by Column1 ) b on a.Column1=b.Column1 and a.Column4=b.Column4 order by a.Column1

--測試結果 方法2:

Column1    Column2     Column3    Column4                                               
---------- ----------- ---------- ------------------------------------------------------
A          11          am         1999-01-02 00:00:00.000
B          13          bm         1999-01-04 00:00:00.000
C          15          cm         1999-01-06 00:00:00.000

http://www.west263.com/www/info/28485-1.htm