求达人一句sql语句 谢谢

[莫鸣]

表结构大概是这样子的
zh name flag sd zj
1 zhang 1 23 26
1 zhang 2 26 33
1 zhang 3 33 36
.
.
2 li 1 56 89
2 li 2 89 103
2 li 3 103 106
2 li 4 106 108
.
.
.
3 wang 1 225 228
3 wang 2 228 230
....

具体是这样子的。flag 字段在增 ， 然后求 flag 最大的那列 sd 和zj的值然后把 其他的列删除了。
比如上面的要求的结果是：
zh name flag sd zj
1 zhang 3 33 36
2 li 4 106 108
3 wang 2 228 230


请问这句sql如何写？

[莫鸣]

谢谢，大家答案都不错。特别是前两位大哥。得到了句子，我慢慢研究先。

[快溜]

delete a from tb a where exists(select 1 from tb where zh=a.zh and flag>a.flag)

[Liu_cy88]

select * from Table t where flag = (select max(flag) from Table ts where t.zh = ts.zh)

[xuam]

select * from TB where not exists(select from TB a where TB.ID =a.id and TB.flag <a.flag)

[qqqqqqqq_2013]

每行应该都有一个唯一的id吧？

select * from Table where id in (select id from Table where flag in (select max(flag) from Table group by zh))

希望对楼主有所启发

[qqqqqqqq_2013]

或者这样

 select * from Table a where not exists(select 1 from Table where zh=a.zh and flag>a.flag) 

[xiaosa_21]

create table tb1 
(
zh int not null,
name nvarchar(10),
flag int not null,
sd int,
zj int
)

insert into tb1
select 1,'zhang',1,23,26
union all
select 1,'zhang',2,26,33
union all
select 1,'zhang',3,33,36
union all
select 2,'li',1,56,89
union all
select 2,'li',2,89,103
union all
select 2,'li',3,103,106
union all
select 2,'li',4,106,108
union all
select 3,'wang',1,225,228
union all
select 3,'wang',2,228,230

select zh,name,flag,sd,zj,sd+zj as summ from (
select zh,name,flag,sd,zj,row_number() over(partition by zh order by flag desc) as rn from tb1) a
where rn=1

[我的tcl]

select zh,name,flag,sd,zj,row_number() over(partition by zh,name order by flag desc) as rn from tb1from