John Davidson

Message:

i am joining the latest relation row with the following code.

SELECT pos.* FROM users AS usr LEFT JOIN posts AS pos ON pos.user_id = usr.id WHERE

                                pos.id = (

                                    SELECT MAX(id)

                                    FROM posts

                                    WHERE user_id = usr.id

                                );

if I want to join the second last row only here is the code

SELECT pos.* FROM users AS usr LEFT JOIN posts AS pos ON pos.user_id = usr.id WHERE

                                pos.id = (

                                    SELECT id

                                    FROM posts

                                    WHERE user_id = usr.id

                                    ORDER BY id DESC LIMIT 1,1

                                ); 

The question is what i can do if I want to join the latest five rows again in each relation?

---

My Answer:
You can achieve this by using a subquery to select the latest five rows for each user in the posts table and then joining that subquery with the users table. Here is an example query:


SELECT pos.*
FROM users AS usr
LEFT JOIN (
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY id DESC) AS rn
FROM posts
) AS latest_posts
WHERE rn <= 5
) AS pos ON pos.user_id = usr.id


In this query:
- We first use a subquery to assign a row number to each row in the posts table for each user, ordered by id in descending order.
- We then filter the subquery to only include rows where the row number is less than or equal to 5, effectively selecting the latest five rows for each user.
- Finally, we left join this subquery with the users table on the user_id column to get the desired result.