帮想一个用一条SQL语句取的记录的方法! ( 积分: 300 )

有如下表:
-----------------------------------------
name | icount
-----------------------------------------
n1 | 1
-----------------------------------------
n1 | 2
-----------------------------------------
n2 | 2
-----------------------------------------
n2 | 3
-----------------------------------------
有没有办法用一条SQL语句从取相同"name"字段中"iCount"最大的记录,结果因该如下:
-----------------------------------------
name | icount
-----------------------------------------
n1 | 2
-----------------------------------------
n2 | 3
-----------------------------------------

select [name], max(icount) from 表 group by [name]

前面说错了:
表结构应该如下:
------------------------------------------------
name | icount | address
------------------------------------------------
n1 | 1 | bj
------------------------------------------------
n1 | 2 | nj
------------------------------------------------
n2 | 2 | hz
------------------------------------------------
n2 | 3 | zj
------------------------------------------------
有没有办法用一条SQL语句从取相同"name"字段中"iCount"最大的记录,结果因该如下:
------------------------------------------------
name | icount | address
------------------------------------------------
n1 | 2 | nj
------------------------------------------------
n2 | 3 | zj
------------------------------------------------

select a.* from 表 a,
(select [name], max(icount) as c from 表 group by [name]) b
where a.[name]=b.[name] and a.icount = b.c

select a.*
from 表 as a
join
(select [name], max(icount) as icount from 表 group by [name] ) as b
where a.name=b.name and a.icount=b.icount

select * from table as a where a.icount=(select max(icount) from table where table.[name]=a.[name] )
没环境测试

select a.* from 表 a,
(select [name], max(icount) as c from 表 group by [name]) b
where a.[name]=b.[name] and a.icount = b.c

这个最正确了.小kiss的东西

多人接受答案了。