/* Known data:
Column1 column2 column3 column4
A 10 am 00:00:00. 000
A 11 am 00:00:00. 000
B 12 BM 00:00:00. 000
B 13 BM 00:00:00. 000
C 14 cm 00:00:00. 000
C 15 cm 00:00:00. 000
Required data:
column1 column2 column3 column4
A 11 am 00:00:00. 000
B 13 BM 00:00:00. 000
C 15 cm 00:00:00. 000 */
-- Data Loading
Create Table # T (column1 varchar (10), column2 int, column3 varchar (10), column4 datetime)
Insert # T select 'A', 10, 'am', '2017-1-1'
Union all select 'A', 11, 'am', '2017-1-2'
Union all select 'B', 12, 'bm ', '2014-1-3'
Union all select 'B', 13, 'bm ', '2017-1-4'
Union all select 'C', 14, 'cm ', '2017-1-5'
Union all select 'C', 15, 'cm ', '2017-1-6'
-- Test statement Method 1:
Select a. * from # T
Where (A. column4) = (select top 1 (column4) from # t where column1 = A. column1 order by column4 DESC)
-- Test Result:
Column1 column2 column3 column4
-------------------------------------------------------------------------------------
A 11 am 00:00:00. 000
B 13 BM 00:00:00. 000
C 15 cm 00:00:00. 000
-- Test statement Method 2: (optimal efficiency)
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
-- Test Result Method 2:
Column1 column2 column3 column4
-------------------------------------------------------------------------------------
A 11 am 00:00:00. 000
B 13 BM 00:00:00. 000
C 15 cm 00:00:00. 000
Http://www.west263.com/www/info/28485-1.htm