如何抽取某字段值最大的记录

[clkun]

表a(id是自动编号)
id name shu
1 a 1
2 a 2
3 a 3
4 b 1
5 b 2
6 c 1
7 d 1

要求就是name值相同的取出 shu 的值最大的一条记录，上面过滤一下结果如果下：

id name shu
3 a 3
5 b 2
6 c 1
7 d 1

先谢了。

[SQL77]

SELECT * FROM TB T WHERE NOT EXISTS(SELECT 1 FROM TB WHERE ID<>T.ID AND NAME=T.NAME AND T.SHU<SHU)

[drysea]

[Quote=引用 2 楼 ks_reny 的回复:]
SQL codeSelect*from tb awherenotexists (select1from name=a.nameand a.shu>shu)
[/Quote]

up

[flairsky]

select name,max(shu) from a
group by name

[edithbianyuhua]

[Quote=引用 10 楼 flairsky 的回复:]
select name,max(shu) from a
group by name

[/Quote]

测试过没有问题

[izbox]

select name,max(shu) from a
group by name

[ks_reny]

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

[feixianxxx]

select name,max(shu) from a
group by name
--or
Select * from tb  a where not exists (select 1 from a where name=a.name and a.shu>shu
--or
select * from tb a aa where shu =(select max(shu) from a where aa.name=name )

[izbox]

Select * from #a  a where not exists (select 1 from #a b where b.name=a.name and b.depID>a.depID)

[jiangshun]

if object_id('[表a]') is not null drop table [表a]
create table [表a]([id] int,[name] varchar(1),[shu] int)
insert [表a]
select 1,'a',1 union all
select 2,'a',2 union all
select 3,'a',3 union all
select 4,'b',1 union all
select 5,'b',2 union all
select 6,'c',1 union all
select 7,'d',1

select * from [表a] t where not exists(select 1 from [表a] where t.[name]=[name] and t.id<id)


/*
id          name shu         
----------- ---- ----------- 
3           a    3
5           b    2
6           c    1
7           d    1

（所影响的行数为 4 行）
*/

drop table [表a]

[dawugui]

select m.* from tb m where not exists(select 1 from tb n where name = m.name and shu > m.shu)

select m.* from tb m where shu = (select max(shu) from tb n where name = m.name)

[hecker728]

declare @t table(id int,name varchar(5),shu int)
insert @t
select 1,'a',1 union all
select 2,'a',2 union all
select 3,'a',3 union all
select 4,'b',1 union all
select 5,'b',2 union all
select 6,'c',1 union all
select 7,'d',1 

select a.id,a.name,b.shu from @t a right join
(select name,max(shu) as shu from @t group by name) b on
a.name=b.name where a.shu=b.shu order by id asc

/*-------------------------
   id  name  shu 
    3    a    3 
    5    b    2 
    6    c    1 
    7    d    1 
---------------------------*/