请教一个简单的sql语句

[广州人]

id date name
1 2011-09-09 10:00:00 cxd
2 2011-09-08 10:00:00 cxd
3 2011-09-07 10:00:00 cxd
4 2011-09-08 10:00:00 yejin
5 2011-09-09 10:00:00 yejin
6 2011-09-10 10:00:00 yejin

我需要返回的数据是以CXD来分组，时间最早的一条
返回的结果是：
3 2011-09-07 10:00:00 cxd
4 2011-09-08 10:00:00 yejin

请教各位高手

[chuanzhang5687]

select min(date),name from tb group by name

[chuanzhang5687]

select id ,date , name from tb t
where not exists(select 1 from tb where t.id = id and t.date<date)

[广州人]

还要有编号也要返回出来

[areswang]

select * from tb t
where not exists(select 1 from tb where name=t.name and dt<t.dt)

[水族杰纶]

;with tmp
as
(select rownum=row_number()over(partition by CXD order by [date]),
 *
 from tb)
select * from tmp
where rownum=1

[chuanzhang5687]

create table tb 
(
	id		int,
	date	datetime,
	name	varchar(10)
)
insert into tb values(1,'2011-09-09 10:00:00','cxd')
insert into tb values(2,'2011-09-08 10:00:00','cxd')
insert into tb values(3,'2011-09-07 10:00:00','cxd')
insert into tb values(4,'2011-09-08 10:00:00','yejin')
insert into tb values(5,'2011-09-09 10:00:00','yejin')
insert into tb values(6,'2011-09-10 10:00:00','yejin')

select id ,date , name from tb t
where not exists(select 1 from tb where t.name = name and t.date>date)
/*
id,date,name
3,2011-09-07 10:00:00.000,cxd
4,2011-09-08 10:00:00.000,yejin

(2 行受影响)

修改一下

[--小F--]

select * from tb t where date=(select max(date) from tb where name =t.name)

[dawugui]

select t.* from tb t where date = (select min(date) from tb where CXD = t.CXD) order by t.id
select t.* from tb t where not exists (select 1 from tb where CXD = t.CXD and date < t.date) order by t.id