Seeking the latest data in the record!
Known data:
Column1 Column2 Column3 Column4a 10 AM 1999-01-01 00: 00.000A 11 AM 1999-01-02 00: 00:00.000B 12 BM 1999-01-03 00: 00:00.000B 13 BM 1999-01-04 00: 00:00.000c 14 cm 1999-01-05 00: 00: 00.000C 15 cm 1999-01-06 00: 00: 00.000
Require data:
Column1 Column2 Column3 Column4a 11 AM 1999-01-02 00: 00:00.000B 13 BM 1999-01-04 00: 00: 00.000C 15 cm 1999-01-06 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', '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 'ELE SELECT' C ', 14,' CM ',' 1999-1-5'Union All Select 'C', 15, 'cm', '1999-1-6'
- Test statement method 1:
SELECT A. * From #t a where (a.column4) = (SELECT TOP 1 (Column4) from #t where column1 = a.column1 Order by Column4 DESC)
--Test Results:
Column1 column2 column3 column4 -------------------------------- -------------------------------------- A 11 AM 1999-01-02 00:00 00.000b 13 BM 1999-01-04 00: 00:00.000C 15 cm 1999-01-06 00: 00: 00.000 - Test statement method 2: (best efficiency)
Select a. * from # # (Select Column1, Column4 = Max (Column4) from #t group by column1) b on a.column1 = b.column1 and a.column4 = B.Column4Order by a.column1
- Test results method 2:
Column1 column2 column3 column4 -------------------------------- -------------------------------------- A 11 AM 1999-01-02 00:00 : 00.000B 13 BM 1999-01-04 00: 00:00.000C 15 cm 1999-01-06 00: 00: 00.000
