Fetch multiple rows using max()

Question

I have written the below query which gives me the resultant set -

select
    a.character_name,
    b.planet_name,
    sum(b.departure - b.arrival) AS screen_time
from characters a
inner join timetable b
    on a.character_name = b.character_name
group by a.character_name, b.planet_name;

Resultant set :

Character_name | planet_name | Screen_time
C-3 PO       Bespin         4
C-3 PO       Hoth           2
C-3 PO       Tatooine       4
Chewbacca    Bespin         4
Chewbacca    Endor          5
Chewbacca    Hoth           2
Chewbacca    Tatooine       4

Now, how do I select the planet_name and character_name of each character having the max(screen_time). For ex, For C-3 PO, two rows to display

C-3 PO | Bespin
C-3 PO | Tattoine

For Chewbacca, one row to display

Chewbacca | Endor

The problem I am facing is because I am not able to implement the conditions to the intermediate table.

Answer 1

EDIT (deleted my previous answer)

You can also achieve the same with a correlated subquery in the HAVING clause:

select
    a.character_name,
    b.planet_name,
    sum(b.departure - b.arrival) as screen_time
from characters a
inner join timetable b
    on a.character_name = b.character_name
group by a.character_name, b.planet_name
having sum(b.departure - b.arrival) = (
    select 
        max(tmp.screen_time)
    from (
        select b.character_name, sum(b.departure - b.arrival) as screen_time
        from timetable b
        group by b.character_name, b.planet_name) tmp
    where a.character_name = tmp.character_name
    group by tmp.character_name);

Given your question, I'm not sure if you want to retrieve the screen_time or just character_name and planet_name. If you just want the last two columns, remove sum(b.departure - b.arrival) as screen_time from the main query:

select
    a.character_name,
    b.planet_name
from characters a
inner join timetable b
    on a.character_name = b.character_name
group by a.character_name, b.planet_name
having sum(b.departure - b.arrival) = (
    select 
        max(tmp.screen_time)
    from (
        select b.character_name, sum(b.departure - b.arrival) as screen_time
        from timetable b
        group by b.character_name, b.planet_name) tmp
    where a.character_name = tmp.character_name
    group by tmp.character_name);   

PS: my previous answers did not work. Sorry about that.