Find record with maximum value in related table

Question

How do I get the USER_PASSWORD ID of the most recently created user_password for each user?

USER Table

USER_ID	USERNAME
1	USER1
2	USER2
3	USER3

USER_PASSWORD Table

ID	USER_ID	PASSWORD	CREATED
1	1	PASSWORD1	2020-01-01
2	1	PASSWORD2	2020-02-01
3	1	PASSWORD3	2020-03-01
4	2	PASSWORD4	2020-04-01
5	2	PASSWORD5	2020-05-01
6	2	PASSWORD6	2020-06-01
7	3	PASSWORD7	2020-07-01
8	3	PASSWORD8	2020-08-01
9	3	PASSWORD9	2020-09-01

Currently I'm using the sloppy query below

SELECT * FROM
(SELECT * FROM USER) USERS
    JOIN (SELECT USER_ID, MAX(CREATED) MAX FROM USER_PASSWORD GROUP BY USER_ID) MAXPASSWORDS
         ON USERS.USER_ID = MAXPASSWORDS.USER_ID
    JOIN (SELECT * FROM USER_PASSWORD) PASSWORDS
         ON USERS.USER_ID = PASSWORDS.USER_ID AND MAXPASSWORDS.MAX = PASSWORDS.CREATED
ORDER BY USERS.USERNAME;

Answer 1

On the join, you can specify the max function as below.

select u.user_id, up.id as user_password_id, up.created
from users u 
inner join user_passwords up 
  on up.user_id = u.user_id 
  and up.created = (
    select max(created) from user_passwords where user_id = u.user_id
  );

So basically when doing the join, it will find the latest password created (max(created)) for each user who is being joined.

Working example @DB Fiddle on Postgres 12