Re: How to select all parent row and most recent/last child row only

[Peter Brawley]

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.