求sql 语句 - .NET技术 / ASP.NET

id bh value time
K0011 01 25 2010-05-10 05:08:10
K0012 01 35 2010-05-10 05:10:10
K0013 01 35 2010-05-10 05:11:10
K0014 02 20 2010-05-10 05:08:11
K0015 02 50 2010-05-10 05:09:12
K0016 03 30 2010-05-10 05:08:10
K0017 03 25 2010-05-10 05:10:10
K0018 03 35 2010-05-10 05:10:10
K0019 01 35 2010-05-10 06:11:10
K0020 02 20 2010-05-10 06:12:10
K0021 02 40 2010-05-10 06:13:10
K0022 03 30 2010-05-10 06:14:10

所有的
id bh value time
K0019 01 35 2010-05-10 06:11:10
K0015 02 50 2010-05-10 05:09:12
K0018 03 35 2010-05-10 05:10:10

生成所有编号最大值，如果碰到最大值有相同的则取时间最大的那个。

---

;wit tb as
(
  select xuhao = row_number() over (partition by bh order by value desc,time desc),
  id,bh,value,time
  from table
)

select id,bh,value,time
from tb
where xuhao=1

---

引用

;wit tb as
(
select xuhao = row_number() over (partition by bh order by value desc,time desc),
id,bh,value,time
from table
)

select id,bh,value,time
from tb
where xuhao=1

正解，针对sql2005以上版本

---

C# code

---

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHig