By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE notification(id INT,subject VARCHAR(50),date_add VARCHAR(50));
INSERT INTO notification
VALUES( 1,'Subject1', '2020-04-01 12:06:00'),
( 2,'Subject2', '2020-04-18 19:12:59'),
( 3,'Subject3', '2020-04-21 13:46:01'),
( 4,'Subject4', '2020-04-21 13:46:01');
CREATE TABLE notification_post(id INT,user_id INT,notification_id INT,description VARCHAR(50),date_add VARCHAR(50));
INSERT INTO notification_post
VALUES( 1,1,1, 'Text1', '2020-04-01 12:06:00'),
( 2,2,1, 'Text2', '2020-04-20 19:12:59'),
( 3,3,1, 'Text3', '2020-04-21 19:44:36'),
( 4,2,2, 'Text1', '2020-04-21 19:48:24'),
( 5,1,2, 'Text2', '2020-04-21 19:55:00');
SELECT p1.notification_id, p1.user_id, n.Subject, p1.description, p2.post_date
FROM notification_post p1
JOIN notification n
ON p1.notification_id = n.id
JOIN ( SELECT notification_id, MAX(date_add) as post_date
FROM notification_post
GROUP BY notification_id ) p2
ON p2.notification_id = p1.notification_id
AND p2.post_date = p1.date_add
| notification_id | user_id | Subject | description | post_date |
|---|---|---|---|---|
| 1 | 3 | Subject1 | Text3 | 2020-04-21 19:44:36 |
| 2 | 1 | Subject2 | Text2 | 2020-04-21 19:55:00 |