Extract multiple lines with max ()
I wrote the following query which gives me a result 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;
Result 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 can I select the planet_name and character_name of each character having max (screen_time). For example, For C-3 PO, two lines to display
C-3 PO | Bespin
C-3 PO | Tattoine
For Chewbacca one line to display
Chewbacca | Endor
The problem I am facing is that I am unable to implement conditions for the staging table.
source to share
EDIT (deleted my previous answer)
You can also achieve the same with a correlated subquery in a sentence HAVING
:
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);
Considering your question, I'm not sure if you want to get screen_time
or just character_name
and planet_name
. If you only 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 didn't work. Excuse me.
source to share
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 how it works in sqlfiddle mode
But don't worry, performance shouldn't be as bad as it might seem at first glance. MySQL is often smart enough not to run the same query twice.
There are other ways to do the same. You can also try them out if you like: Rows holding the group maximum of a specific column
source to share