--时间最近，应该是max
select * from tablename a
where date=(select max(date) from tablename where ID =a.ID )

------解决方案--------------------
SQL code

select * from tb where not exists(select 1 from tb a where a.date>date and a.id=id and a.name=name)

------解决方案--------------------
SQL code
create table tb
(id int,
 name varchar(10),
 [date] date)
 insert into tb select 1,'A', '2012-1-1' union all
  select 2 ,'B', '2012-1-2' union all
  select 2 ,'B', '2012-1-3' union all
  select 3 ,'C', '2012-1-4' 
  go
select id,name,[date]=MAX([date]) from tb group by id,name
id    name    date
1    A    2012-01-01
2    B    2012-01-03
3    C    2012-01-04

(3 行受影响)

------解决方案--------------------
SQL code

use tempdb;
/*
create table A
(
    ID int not null,
    name nvarchar(10) not null,
    [date] nvarchar(10) not null
);
insert into A values
(1,'A','2012-1-1'),
(2,'B','2012-1-2'),
(2,'B','2012-1-3'),
(3,'C','2012-1-4');
*/
select B.ID,B.name,B.[date]
from
(
    SELECT A.ID,A.name,A.[date],
    row_number() over(partition by A.name order by A.ID,A.[date] desc) as [orderno]
    FROM A
) as B
where B.orderno = 1;

------解决方案--------------------
select * from table a where not exists(select 1 from table b where a.id = b.id and a.date < b.date)