Need assistance with an SQL Query
Here are the associated tables:
movie
(
mvnumb int,
mvtitle char(100),
yrmde int,
mvtype char(9),
crit int,
mpaa char(6),
no开发者_StackOverflow中文版ms int,
awrd int,
dirnumb int
)
director
(
dirnumb int,
dirname char(36),
dirborn int,
dirdied int
)
My goal is to construct an SQL query that lists the name of the director who has received the maximum number of awards (awrd). I can't seem to get this to work ... any help would be greatly appreciated.. thanks so much.
Something like this (SQL Server):
select top 1 d.dirname,sum(awrd) awrd
from director d
inner join movie m
on m.dirnumb=d.dirnumb
group by d.dirname
order by sum(awrd) desc
Or, on Oracle:
select * from (
select d.dirname, sum(awrd) awards
from director d
inner join movie m
on m.dirnumb=d.dirnumb
group by d.dirname
order by sum(awrd) desc )
where rownum<2;
EDIT: modified Oracle query, as @pilcrow suggested.
精彩评论