Newest Blog Post for Each User

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

The inner SELECT chooses the user_id, 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 user_id, created_at pair match those produced by the inner SELECT.

Advertisements
%d bloggers like this: