I'm sure its a simple question and I suppose it was asked many times, but I just can't figure it out from other answers, sorry.
I use recent versions of PostgreSQL and MySQL. I have 2 tables:
CREATE TABLE authors ( id INT, name VARCHAR ) CREATE TABLE posts ( id INT, author_id INT, text VARCHAR, date DATE )
I need to select one most recent post for each author. Thanks!
Thanks, both links provide answers to my question with some exception. All the following queries give the same result (which one is the most efficient btw?) The issue is when there is more than one post from the same author with the same date. Then the returned result set contains all such posts. How should I modify these queries to return exactly one post per author?
SELECT p1.* FROM posts p1 LEFT JOIN posts p2 ON p1.author_id = p2.author_id AND p1.date < p2.date WHERE p2.author_id IS NULL ORDER BY p1.author_id; SELECT p1.* FROM posts p1 INNER JOIN ( SELECT author_id, MAX(date) AS max_date FROM posts GROUP BY author_id) p2 ON p1.author_id = p2.author_id AND p1.date = p2.max_date ORDER BY p1.author_id; SELECT * FROM posts p1 WHERE date = (SELECT MAX(p2.date) FROM posts p2 WHERE p1.author_id = p2.author_id) ORDER BY author_id; SELECT * FROM ( SELECT author_id, MAX(date) date FROM posts GROUP BY author_id ) p1 INNER JOIN posts p2 USING (author_id, date) ORDER BY author_id;