Query data of a table along with latest record of another table

Question

I am working on a forum like website where I have to query list of categories along with its latest post in the given category.

These are the table schemas

create table forum_posts (
    forum_post_id text not null unique,
    forum_post_name text not null,
    user_id text not null references users(user_id),
    forum_category_id text not null references forum_categories(forum_category_id),
    forum_post_content text not null,
    forum_post_is_active boolean not null default true,
    forum_post_created_timestamp timestamptz not null default now(),
    -- gets updated when user
    --
    -- have edited the post
    -- new reply
    -- user have un-deactivate (goes from deactivated to activated) the post.
    forum_post_last_active_timestamp timestamptz not null default now(),
    -- when forum_post_is_active gets switch to false, deactivated timestamp gets updated
    forum_post_deactivated_timestamp timestamptz,
    forum_post_is_category_based_announcement boolean not null default false,
    forum_post_is_global_announcement boolean not null default false,
    primary key (forum_post_id)
);

create table forum_categories (
    forum_category_id text not null unique,
    forum_category_name text not null unique,
    forum_category_representative_id text not null unique,
    user_id text not null,
    forum_category_color_theme text not null default '#000000',
    forum_category_created_timestamp timestamptz not null default now(),
    primary key (forum_category_id),
    foreign key (user_id) references users(user_id)
);

What I wanted to query is to query a list of categories with the latest post in forum_posts table. I have tried to create the query below but I am not sure how to write the where clause

select
    forum_categories.forum_category_id,
    count(forum_posts.forum_post_id) as post_count,
    forum_posts.forum_post_id as latest_post_id,
    forum_posts.forum_post_created_timestamp as latest_post_timestamp
from forum_categories
inner join forum_posts on forum_categories.forum_category_id = forum_posts.forum_category_id
where -- how do I write the where clause here.

Thank you for any help.

I think you don't need join forum_categories. because in table forum_posts, `forum_category_id text not null references forum_categories` means that forum_category_id value must existed in table forum_categories. — jian, Feb 21 '23 at 11:38
I don't understand what you want your WHERE clause to do. Do you want to select every category, or a handful of categories provided by parameters, or what? — jjanes, Feb 21 '23 at 15:14
@jjanes I wanted to select just one category with the latest `forum_posts.forum_post_created_timestamp`. Take a look at https://forum.scssoft.com/ you will see to the right they have the latest post there. I wanted to know how to get that. Thank you. — grindarius, Feb 21 '23 at 16:58
You say you just one category, but that link seems to show the most recent post for each category (called, apparently, "forum"), not just one of them. — jjanes, Feb 21 '23 at 17:29

JohnH · Answer 1 · 2023-03-03T09:19:48.343

The following will return the most recent post for each category with a post:

SELECT DISTINCT ON (forum_category_id) forum_category_id,
                                       forum_post_id,
                                       forum_post_created_timestamp
  FROM forum_posts
  ORDER BY forum_category_id, forum_post_created_timestamp DESC, forum_post_id DESC;

Adding forum_post_id to the sort criteria makes the query deterministic if multiple post in a category could have the same timestamp. If each post in a category is guaranteed to have a different timestamp, then forum_post_id is redundant. If post IDs within a category increase monotonically with time, then sorting by forum_post_created_timestamp is unnecessary.

score 0 · Answer 2 · answered Feb 21 '23 at 19:04

I have solved some part of the question by using with statement

WITH first_row AS (
SELECT RANK() OVER (PARTITION BY forum_posts.forum_category_id ORDER BY forum_posts.forum_post_created_timestamp DESC) AS created_rank,
         forum_posts.forum_category_id,
         forum_posts.forum_post_id,
         forum_posts.forum_post_created_timestamp
    FROM forum_posts
)
SELECT  created_rank,
      forum_category_id,
      forum_post_id,
      forum_post_created_timestamp
FROM first_row
WHERE created_rank= 1

thank you to a guy on reddit.

Query data of a table along with latest record of another table

2 Answers2