select top 1 sname,avg(score) from s join sc on s.snum=sc.snum group by sname order by avg(score) desc
create function fun_findmax() returns nvarchar(max) as begin declare @name nvarchar(20) ;with c1 as ( select AVG(score) as score,snum from sc group by snum ), c2 as ( select MAX(score) as score,snum from c1 ) select @name=s.sname from s,c2 where s.snum=c2.snum return @name end
select top 1 sname,avg(score) from s join sc on s.snum=sc.snum
group by sname order by avg(score) desc
returns nvarchar(max)
as
begin
declare @name nvarchar(20)
;with c1 as
(
select AVG(score) as score,snum from sc group by snum
),
c2 as
(
select MAX(score) as score,snum from c1
)
select @name=s.sname from s,c2 where s.snum=c2.snum
return @name
end