How do I filter a set of child records to get a single child record for each parent? [duplicate]

Question

Sorry if it's a simple question but I can't wrap my head around it. I have two tables, users and carts. For each user record I need to get it's most recent cart (ordered by created_at desc).

I've managed to get all carts for each user with:

select users.id as user_id, c.id as cart_id from carts c
join users on c.user_id = users.id


user_id | cart_id | created_at
1         1         2022-08-01
1         2         2022-08-05 
1         3         2022-08-10
2         4         2022-10-10
2         5         2022-11-11

Now I want to remove duplicate user_id rows and get the most recent cart for each user, but I can't manage to use this same query as a subquery without getting a

subquery must return a single value error

What is the correct way to write this?

Answer 1

See warnings at the bottom.

CREATE TABLE carts (
  user_id int,
  cart_id int,
  created_at date,
  primary key (cart_id)
);

INSERT INTO carts
  (user_id, cart_id, created_at)
VALUES
  (1,         1,         '2022-08-01'),
  (1,         2,         '2022-08-05'),
  (1,         3,         '2022-08-10'),
  (2,         4,         '2022-10-10'),
  (2,         5,         '2022-11-11');

Select the most recent date for each user.

select user_id, max(created_at) as max_created_at 
from carts
group by user_id;
--
user_id  max_created_at
2        2022-11-11
1        2022-08-10

Join on that result.

select c1.user_id, c1.cart_id, c1.created_at
from carts c1
inner join (select user_id, max(created_at) as max_created_at 
            from carts 
            group by user_id) c2 
  on c1.user_id = c2.user_id and c1.created_at = c2.max_created_at;
--
user_id  cart_id  created_at
1        3        2022-08-10
2        5        2022-11-11

Warnings

• In the real world, users might create more than one cart in one day.
• created_at should probably be a timestamp, not a date.