找最大中的任意一个

[tanshihou]

有这样一个表
学号 班级 成绩
-------------------------
1 1 70
2 1 71
3 1 72
4 2 73
5 2 75
6 2 75

想得到每个班级成绩最高的那个人的学号，如果最高成绩有N个人，任意取其中一个

学号
------
3
6
这里由于学号5,6的成绩一样，所以取5也行，取6也行

谢谢了

[阳泉酒家小当家]

select 学号
from
(
select *,
       ROW_NUMBER() over(partition by 班级 order by 成绩 desc) as rownum
from t
)a
where rownum = 1

[唐诗三百首]

select 学号,班级,成绩
 from (select 学号,班级,成绩,
              row_number() over(partition by 班级 order by 成绩 desc) 'rn'
       from [表名]) t
where rn=1

[阳泉酒家小当家]

create table t
(
学号 int,   
班级 int,  
成绩 int
)

insert into t
select 1 ,    1  ,    70 union all
select 2  ,   1  ,    71 union all
select 3  ,   1   ,   72 union all
select 4  ,   2  ,    73 union all
select 5  ,   2  ,    75 union all
select 6  ,   2 ,     75

select 学号
from
(
select *,
       ROW_NUMBER() over(partition by 班级 order by 成绩 desc) as rownum
from t
)a
where rownum = 1
/*
学号
3
5
*/

[ai_li7758521]

;WITH S AS
(
	SELECT 学号,班级,成绩,row_number() over(partition by 班级 order by 成绩 desc) AS R
	FROM [表名]
)

SELECT 学号,班级,成绩 
FROM S 
WHERE R=1

[Mr_Nice]

SELECT  DISTINCT C.学号
FROM    dbo.TB AS B
        CROSS APPLY ( SELECT TOP ( 1 )
                                A.学号
                      FROM      TB A
                      WHERE     A.班级 = B.班级
                   ORDER BY 班级,成绩 desc ) AS C
                   
/*
3
5*/