再次询问

[wnsr]

http://bbs.csdn.net/topics/390894356
create table ryb (dah varchar(10),xm varchar(10),sm varchar(10))
insert into ryb
select '430214','张三',null union all
select '123456','张三',null union all
select '5245','张三',null union all
select '14879','李四',null union all
select '45123','李四',null

我要更新姓名相同其中(dah)最长的那条数据且最长之中(最大)的某个字段的sm值为'最长' ，又该怎么写这语句？
例如dah为'430214',更新sm='最长',
dah为'45123',更新sm='最长',其它的sm不更新
(即多了要查出最大的那个)

[xiaodongni]

  update t  set sm='最长'
  from  
 ( select * ,
  row_number()over(partition by  xm order by len(dah) desc,dah desc)as n from  ryb ) t
  where n=1

[沉默肥牛]

WITH a AS (
SELECT ROW_NUMBER()OVER(PARTITION BY xm ORDER BY CAST(dah AS INT)desc) id,* FROM ryb
)
UPDATE ryh SET sm='最长' WHERE dah IN (SELECT dah FROM a WHERE id=1)

[hleb231]

再一种
update  ryb
set sm='最长'
from
(select  top 1 * from  ryb a where len(dah)=(select  max(len(dah)) from  ryb ) 
order by dah desc)a
where ryb.dah =a.dah

[hleb231]

上面没看清要分组，楼上两位的写法简单，只是提供一下别的写法
update #temp
set sm='最长'
from
(select  MAX(dah) dah from  #temp a where len(dah)in(select  max(len(dah)) from #temp group by XM) 
group by xm) a
where #temp.dah =a.dah