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.

either you've to redesign your table relations or you have to write a procedure. — Player_Neo, Jun 12 '17 at 07:16

jzeta · Accepted Answer · 2017-06-12T08:59:05.717

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.

fancyPants · Answer 2 · 2017-06-12T11:19:28.277

1

select character_name, planet_name
from (

    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

) sq
where screen_time = (SELECT MAX(ssq.screen_time) FROM (

    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

) ssq WHERE ssq.character_name = sq.character_name 
);

see it working live in an sqlfiddle

But don't worry, the performance should not be as bad as it may first look. MySQL often is smart enough, to not execute the same query twice.

There are also other ways to achieve the same. You can try those, too, if you like: The Rows Holding the Group-wise Maximum of a Certain Column

edited Jun 12 '17 at 11:19

answered Jun 12 '17 at 07:52

fancyPants

50,732
33
89
96

@jzeta I don't think so. Any explanation to that claim? – fancyPants Jun 12 '17 at 09:05
Absolutely not. Without this the max value would be 5 and no result for C3PO would appear. And please don't ever edit someone elses answer to correct code, no matter if you're right or not. If you think something is wrong, leave a comment. Apart from that, I'm still absolutely sure, my answer is correct. – fancyPants Jun 12 '17 at 09:52
And what is your sqlfiddle supposed to prove? This is nowhere close to OP's desired result. – fancyPants Jun 12 '17 at 09:54
I wasn't offended, just pointing out how things work here. Anyway, I had another look on it and it turns out that I indeed had a bit too much in the WHERE clause by accident, but the character_name comparison still has to remain. Thanks for your hint. – fancyPants Jun 12 '17 at 11:21

Fetch multiple rows using max()

2 Answers2