MySQL Forums
Forum List  »  Newbie

Re: How to select all parent row and most recent/last child row only
Posted by: Peter Brawley
Date: August 03, 2022 02:38PM

Such problems are easier to solve when you think about them inside out ...

1 Given a trap_id, find its most recent row in the child table ...

select trap_id, max(trap_datetime) as latest
from trap_events
group by trap_id;

2 Join that query to the parent table ...

select a.trap_id, b.latest
from trap_detail as a
join (
  select trap_id, max(trap_datetime) as latest
  from trap_events
  group by trap_id
) as b using(trap_id)
order by a.trap_id;



Edited 1 time(s). Last edit at 08/03/2022 02:38PM by Peter Brawley.

Options: ReplyQuote


Subject
Written By
Posted
Re: How to select all parent row and most recent/last child row only
August 03, 2022 02:38PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.