Grouping rows by "name" from one table and ordering by "timestamp" from other table in a many-to-many relationship with MySQL

I have the following 3 MySQL tables, ‘posts’ can have many players and many ‘players’ can have many ‘posts’. These two are linked together with the players_posts_links table. Each player’s name is unique.

`posts`
| id  | title             | timestamp  |
|-----|-------------------|------------|
| 1   | Hello world       | 2020-09-16 |
| 2   | My favorite songs | 2020-07-01 |
| 3   | Another post      | 2023-04-01 |
| 4   | Gaming together   | 2023-05-03 |

`players`
| id  | name |
|-----|------|
| 1   | John |
| 2   | Jane |
| 3   | Mark |

`players_posts_links`
| id  | player_id | post_id |
|-----|-----------|---------|
| 1   | 1         | 3       |
| 2   | 2         | 1       |
| 3   | 2         | 2       |
| 4   | 3         | 4       |
| 5   | 1         | 4       |

I want to retrieve only the most recent post for each player. When executing the following query below, it does not always return the most recent post by a player.

SELECT players.name, any_value(posts.title)
FROM posts
         LEFT JOIN players_posts_links link on posts.id = link.post_id
         LEFT JOIN players on link.player_id = players.id
GROUP BY players.name
ORDER BY max(posts.timestamp) DESC

The expected result should be:

| posts.id | posts.title     | players.name |
|----------|-----------------|--------------|
| 4        | Gaming together | John         |
| 4        | Gaming together | Mark         |
| 1        | Hello world     | Jane         |

>Solution :

WITH cte AS (
    SELECT po.title, 
           po.timestamp, 
           ppl.player_id, 
           ROW_NUMBER() OVER (PARTITION BY ppl.player_id ORDER BY po.timestamp DESC) rn
    FROM posts po
    JOIN players_posts_links ppl on po.id = ppl.post_id
)
SELECT cte.title, 
       cte.timestamp, 
       pl.name 
FROM cte 
JOIN players pl on cte.player_id = pl.id
WHERE cte.rn = 1;