怎么样写这个sql语句？

[menglionel]

建立以下的表结构和数据：
create table table1(
name char,
score int)
go
create table table2(
name char,
group char)
go
insert into table1 values('li',70)
go
insert into table1 values('wu',67)
go
insert into table1 values('zhou',80)
go
insert into table1 values('zhang',56)
go
insert into table2 values('li','AAA')
go
insert into table2 values('wu','BBB')
go
insert into table2 values('zhou','AAA')
go
insert into talbe2 values('zhang','BBB')
go
问题是：怎么样查出平均分(score)最高的那个组的组名（group)?

[Rotaxe]

select top 1 [group] from table2 inner join table1 on table1.name=table2.name group by [group] orderby avg[score] desc

[zjcxc]

select top 1 [group],score=avg(score)
from table1 a join table2 b on a.name=b.name
group by [group]
order by score desc

[zjcxc]

--下面是测试
declare @table1 table(name char(8),score int)
declare @table2 table(name char(8),[group] char(6))

insert into @table1
select 'li',70
union all select 'wu',67
union all select 'zhou',80
union all select 'zhang',56

insert into @table2
select 'li','AAA'
union all select 'wu','BBB'
union all select 'zhou','AAA'
union all select 'zhang','BBB'

--查询
select top 1 [group],score=avg(score)
from @table1 a join @table2 b on a.name=b.name
group by [group]
order by score desc

/*--结果
group score
------ -----------
AAA 75
--*/

[victorycyz]

select top 1 b.group
from table1 a join table2 b on a.name=b.name
group by b.group
order by avg(a.score) desc

注意，不要用[name]关联，因为有重名的可能。

[menglionel]

感谢大家的回复，现在看来大家都是用order排序实现的，能用其他方法实现吗？