CREATE TABLE authors (
author_id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE opinions (
opinion_id INT PRIMARY KEY,
author_id INT,
title VARCHAR(255),
content TEXT,
date DATETIME,
FOREIGN KEY (author_id) REFERENCES authors(author_id)
);
INSERT INTO authors (author_id, name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');
INSERT INTO opinions (opinion_id, author_id, title, content, date) VALUES
(1, 1, 'First from Alice', 'Hello world', '2023-01-01'),
(2, 1, 'Latest from Alice', 'Updated thoughts', '2023-05-01'),
(3, 2, 'Only from Bob', 'Just me here', '2023-03-15'),
(4, 3, 'First from Charlie', 'Hi there', '2023-02-10'),
(5, 3, 'Latest from Charlie', 'Follow-up', '2023-04-20'),
(6, 3, 'Latest from Charlie 2', 'Follow-up', '2023-04-20');
SELECT *
FROM opinions o
JOIN (
SELECT author_id, MAX(date) latest_date
FROM opinions
GROUP BY author_id
) latest
ON latest.author_id = o.author_id
AND latest.latest_date = o.date
JOIN authors a ON o.author_id = a.author_id;