求一条sql语句

[bitm]

如下两个表：
表１
table1

attachid name
100 北京
200 上海
null ....


表２
table2

aid attachid content
1 200 test
3 100 sdfsdf
4 100　 3212
5 100 dsd

table1 跟table2是一对多的关系。。attachid 为两表关链键,table1中attachid为唯一值

如果我用这样的语句:
select * from table1 left join table2 on table1.attachid= table2.attachid
就会查询出多条attachid相同值的记录。如何排出这些值？我只想取一条attachid的值

[bitm]

补充:table1中，如果attachid有值，那么就是唯 一值，也有为null的值

[liangCK]

select a.name,b.aid,b.attachid,b.content
from table1 a left join table2 b
 on a.attachid=b.attachid
group by a.name,b.aid,b.attachid,b.content

[dennis80]

distinct

[kk19840210]

select   distinct table1.*   from   table1   left   join   table2   on   table1.attachid=   table2.attachid 

[marco08]

create table A(
attachid int,
[name] nvarchar(10)
)
insert A select 100,'北京'   
insert A select 200,'上海' 

create table B(
aid int,
attachid int,
content nvarchar(10)   
)
insert B select 1,200,'test'   
insert B select 3,100,'sdfsdf'   
insert B select 4,100,'3212'   
insert B select 5,100,'dsd' 

select * from A
left join (
select * from B as tmp
where not exists(select * from B where attachid=tmp.attachid and aid>tmp.aid)
)B on A.attachid=B.attachid

[wzy_love_sly]

declare @a table (attachid int,name varchar(10))
insert into @a select 100,'北京'
insert into @a select 200,'上海'

declare @b table (aid int,attachid varchar(10),content varchar(10))
insert into @b select 1,200,'test'
insert into @b select 3,100,'sdfsdf'
insert into @b select 4,100,'3212'
insert into @b select 5,100,'dsd'

select * from @a a
left join @b b on a.attachid=b.attachid
where not exists(
select 1 from @b where attachid=b.attachid and aid>b.aid
)

attachid name aid attachid content
100 北京 5 100 dsd
200 上海 1 200 test

[dawugui]

--max(aid)
select m.* , n.* from table1 m,
(
  select t.* from table2 t where aid = (select max(aid) from table2 where attchid = t.attchid)
) n
where m.attachid = n.attachid

--min(aid)
select m.* , n.* from table1 m,
(
  select t.* from table2 t where aid = (select min(aid) from table2 where attchid = t.attchid)
) n
where m.attachid = n.attachid

[marco08]

--result
attachid    name       aid         attachid    content    
----------- ---------- ----------- ----------- ---------- 
100         北京         5           100         dsd
200         上海         1           200         test

（所影响的行数为 2 行）