含有时间字段的Select语句根据Code提取最大时间的记录？

Question

有如下的表结构：insert into DWSFI(SurveyYear,WaterSouceCode,DesignIntake) values('2009-10-08 00:00:00.000','S01',32)
insert into DWSFI(SurveyYear,WaterSouceCode,DesignIntake) values('2009-01-09 00:00:00.000','S01',367)
insert into DWSFI(SurveyYear,WaterSouceCode,DesignIntake) values('2009-10-09 00:00:00.000','R01',43)
insert into DWSFI(SurveyYear,WaterSouceCode,DesignIntake) values('2009-01-07 00:00:00.000','R01',8)由于同一个WaterSouceCode有多条不同时间的记录 我现在只提取时间最大的记录 比如上面执行select的结果如下：2009-10-08 00:00:00.000 S01 32
2009-10-09 00:00:00.000 R01 43请问这条sql语句如何写？？

Answer 1

select *
from DWSFI t
where not exists(select 1 from DWSFI where WaterSouceCode=t.WaterSouceCode and SurveyYear>t.SurveyYear)

Answer 2

select * from DWSFI t
where not exists(select 1 from DWSFI where WaterSouceCode=t.WaterSouceCode and 
DesignIntake>t.DesignIntake)

Answer 3

select * from DWSFI t where not exists(select 1 from DWSFI where WaterSouceCode=t.WaterSouceCode 
and SurveyYear>t.SurveyYear)

Answer 4

select * from DWSFI t
where SurveyYear = (select max(SurveyYear) from DWSFI where WaterSouceCode=t.WaterSouceCode)--orselect * from DWSFI t
where not exists (select 1 from DWSFI where WaterSouceCode=t.WaterSouceCode and SurveyYear>t.SurveyYear)

Answer 5

select
 * 
from
 DWSFI t
where
 SurveyYear = (select max(SurveyYear) from DWSFI where WaterSouceCode=t.WaterSouceCode)