CODEWITHSUNDEEP
sqloracle-database
dvd
dvd

Reputation: 64

sql: selecting specific data concerning two different tables

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

Answers (1)

MrVimes
MrVimes

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

Related Questions