For the front page of LandBrokr we want the newest blog posts. However, we don’t want one user to game the system and fill the front page with just them. Also, we want a link to prior blog posts, still sorted and unique to the user.
In short, I want a list of each user’s newest blog post.
This is surprisingly not straightforward with SQL. My first attempt was:
SELECT DISTINCT ON (user_id) * FROM posts ORDER BY created_at DESC
This is way off. It won’t even work. But I certainly did search the Web for it, so including it here won’t hurt.
Long story, shortened:
SELECT * FROM posts WHERE (user_id,created_at) IN ( SELECT user_id, max(created_at) AS created_at FROM posts GROUP BY user_id) ORDER BY created_at DESC
SELECT chooses the
created_at pair for the user’s newest blog post (uniquified by the
GROUP BY clause). The outer
SELECT simply restricts the result set to those where the
created_at pair match those produced by the inner