How to select the last three records from another table?

Question

MySQL

Good afternoon.

There are 2 tables: users and posts.
Question: how to select all users and display the last three posts from each of them? (no subquery)

I joined 2 tables and got stuck - how do I select the last 3 posts each?

Thanks

Answer 1

WITH `cte` AS (
  SELECT *, ROW_NUMBER() OVER `w` AS `row_num`
    FROM `posts`
    WINDOW `w` AS (
      PARTITION BY `user_id`
      ORDER BY `time` DESC
    )
) SELECT *
  FROM `cte`
  JOIN `users` ON `users`.`id` = `cte`.`user_id`
  WHERE `cte`.`row_num` <= 3