Postgres SQL Join Latest Record From Second Table

Question

A user has many devices. These devices have many events. I want to select all of the users devices and each devices latest event. I tried:

SELECT UD.DEVICE_ID, UD.NICK_NAME, UD.ACTIVATION_DATE, AE.CREATED_AT, AE.ACTION_NAME 
FROM USER_DEVICE UD 
LEFT JOIN (
  SELECT DEVICE_ID, CREATED_AT, ACTION_NAME 
  FROM ADVICE_EVENT 
  WHERE DEVICE_ID IN (
    SELECT DEVICE_ID
    FROM USER_DEVICE
    WHERE ID_TOKEN = 'myToken'
  )
) AE ON UD.DEVICE_ID = AE.DEVICE_ID
WHERE UD.ID_TOKEN = 'myToken' AND UD.DEVICE_ID IS NOT NULL;

I get a list of all the user devices as well as all of it's events. So each device has multiple rows with each event. I only want the latest event.

Answer 1

You can use a lateral join to scan the related table for the latest row. For example:

select *
from user_device d
left join lateral (
  select * from advice_event e where e.device_id = d.device_id
  order by created_at desc limit 1
) x on true
where d.id_token = 'myToken'