MariaDB 10.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE USERS(
    user_id		INT,
  	username	VARCHAR(30)
);
INSERT INTO USERS VALUES
(1   ,     'Marc'),
(2   ,     'Paul'),
(3   ,    'Sofie'),
(4   ,    'Julia');

SELECT * FROM USERS

Records: 4  Duplicates: 0  Warnings: 0




user_id
username


1
Marc

2
Paul

3
Sofie

4
Julia


CREATE TABLE POSTS(
    post_id			INT,
    topic_id		INT,
    poster_id		INT,
    post_time		INT
);
INSERT INTO POSTS VALUES
(4565     ,    6     ,  1         , 999092051),
(4567     ,    6     ,  4         , 999094056),
(4333     ,    6     ,  2         , 999098058),
(7644     ,    6     ,  1         , 999090055);

SELECT * FROM POSTS

Records: 4  Duplicates: 0  Warnings: 0




post_id
topic_id
poster_id
post_time


4565
6
1
999092051

4567
6
4
999094056

4333
6
2
999098058

7644
6
1
999090055


WITH topic_id6 AS (
    SELECT poster_id, post_time, post_id, topic_id,
           ROW_NUMBER() OVER(PARTITION BY topic_id, poster_id ORDER BY post_time DESC) AS rn 
    FROM POSTS
)
SELECT id6.poster_id, id6.post_time, id6.post_id, id6.topic_id, u.*
FROM       topic_id6 id6 
INNER JOIN USERS     u
        ON id6.poster_id = u.user_id
       AND id6.rn = 1


poster_id
post_time
post_id
topic_id
user_id
username


1
999092051
4565
6
1
Marc

2
999098058
4333
6
2
Paul

4
999094056
4567
6
4
Julia

post_id	topic_id	poster_id	post_time
4565	6	1	999092051
4567	6	4	999094056
4333	6	2	999098058
7644	6	1	999090055

poster_id	post_time	post_id	topic_id	user_id	username
1	999092051	4565	6	1	Marc
2	999098058	4333	6	2	Paul
4	999094056	4567	6	4	Julia