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?


1 Answer 1


See warnings at the bottom.

  user_id int,
  cart_id int,
  created_at date,
  primary key (cart_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');

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


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

Not the answer you're looking for? Browse other questions tagged or ask your own question.