求助:sql分组选取最大的一条数据

[ronalho]

id name address score score1
1 a qw 60 80
2 b er 70 60
3 c rr 70 70
4 d tt 80 60
5 e ty 60 70
6 f ui 80 80

如上表：在asp.net中怎样用ＳＱＬ语句按score字段分组选取score1中数值最大的一条数据。

所返回结果如下：
id name address score score1
1 a qw 60 80
3 c rr 70 70
6 f ui 80 80

先谢谢了。

[awant2k]

这句话“在asp.net中怎样用ＳＱＬ语句按score字段分组选取score1中数值最大的一条数据”有点岐义，现给出以下这些SQL语句供选择：

SELECT TOP 1 * FROM [test01] WHERE [score] IN (
SELECT [score] FROM [test01] WHERE [score1]=(
SELECT MAX([score1])
FROM [test01])
GROUP BY [score]) ORDER BY [score] DESC

SELECT * FROM [test01]
WHERE [score] IN (
SELECT TOP 1 [score] FROM [test01]
WHERE [score1]=(
SELECT MAX([score1]) FROM [test01])
GROUP BY [score] ORDER BY [score] DESC)

SELECT TOP 1 * FROM [test01]
WHERE [score] IN (
SELECT [score] FROM [test01]
WHERE [score1]=(
SELECT MAX([score1])
FROM [test01])
GROUP BY [score])
ORDER BY [score] DESC

[tttttttt1234]

select a.id,a.Name,a.Address,a.Score,b.Score1 from
Table a
left join
(
select Score,max(Score1) from Table Group By Score
) b on a.Score=b.Score

[tengjian1981]

TO: tttttttt1234(绿光魔鬼) 你得到结果是:
id name address score score1
1 a qw 60 80
2 b er 70 70
3 c rr 70 70
4 d tt 80 80
5 e ty 60 80
6 f ui 80 80

而不是楼主要的结果.
这样是不是就好了:
select b.id,b.Name,b.Address,a.Score,a.Score1 from
(
select Score,max(Score1) as Score1 from Table Group By Score
) a
left join Table b on a.Score=b.Score and a.Score1=b.Score1

[xxuu503]

不看了，这么多人，肯定有对的答案！