一个应该简单的问题
有以下表结构：
id     value1   value2
a           1               1
a             2             2  
a             1               1
b             3               3
b             2                 2
b             1               1
c               1             1
c               2               2
c               1             1

我想从各id中取出各自的第一条纪录即以下结果：
id     value1       value2
a         1                 1
b           3               3
c           1               1
应该怎么写呢？大家帮帮忙
 


------解决方案--------------------
if object_id( 'pubs..tb ') is not null
drop table tb
go

create table tb(id varchar(1),value1 int,value2 int)
insert into tb(id,value1,value2) values( 'a ',1,1)
insert into tb(id,value1,value2) values( 'a ',2,2)
insert into tb(id,value1,value2) values( 'a ',1,1)
insert into tb(id,value1,value2) values( 'b ',3,3)
insert into tb(id,value1,value2) values( 'b ',2,2)
insert into tb(id,value1,value2) values( 'b ',1,1)
insert into tb(id,value1,value2) values( 'c ',1,1)
insert into tb(id,value1,value2) values( 'c ',2,2)
insert into tb(id,value1,value2) values( 'c ',1,1)
go

select id1=identity(int,1,1) , * into test from tb

select a.id,a.value1,a.value2 from test a,
(select id,min(id1) id1 from test group by id) b
where a.id = b.id and a.id1 = b.id1

drop table tb,test

/*
id value1 value2
---- ----------- -----------
a 1 1
b 3 3
c 1 1

（所影响的行数为 3 行）

*/
------解决方案--------------------
CREATE TABLE #a ( [id] [char] (10), [value1] [int] ,[value2] [int] )
insert into #a(id,value1,value2) values( 'a ',1,1)
insert into #a(id,value1,value2) values( 'a ',2,2)
insert into #a(id,value1,value2) values( 'a ',1,1)
insert into #a(id,value1,value2) values( 'b ',3,3)
insert into #a(id,value1,value2) values( 'b ',2,2)
insert into #a(id,value1,value2) values( 'b ',1,1)
insert into #a(id,value1,value2) values( 'c ',1,1)
insert into #a(id,value1,value2) values( 'c ',2,2)
insert into #a(id,value1,value2) values( 'c ',1,1)


select id1=identity(int,1,1),* into #t from #a

select id,value1,value2 from #t a
where not exists(select 1 from #t
where a.id = id and a.id1 > id1)
drop table #a

drop table #t

/*

id value1 value2
---------- ----------- --