Reputation: 64
So here's the situation, I made two tables:
table 1:
create table Show (
id number(5),
moderator_id number(5),
length number(3),
title varchar(100),
details varchar(30)
);
table 2:
create table Moderator (
id number(5),
name varchar(30),
surname varchar(30)
);
The two tables are in relation: 1(Moderator), N(Show) I need a command, that displays name and surname of moderator, that haves the biggest SUM of length (Show.length represents length of individual show).
Upvotes: 0
Views: 65
Reputation: 3312
This should do it...
select * from (select name,surname from moderator
inner join show on moderator_id = moderator.id
group by moderator.id,name,surname
order by sum(length) desc)
where rownum = 1
Edit: Tested in sqlfiddle using sample data - http://sqlfiddle.com/#!4/1a264/10/0
Upvotes: 2