CODEWITHSUNDEEP
sqlpostgresqljoingreatest-n-per-group
ma11hew28
ma11hew28

Reputation: 126457

SELECT posts and their comments

I have three tables: users, posts, and comments.

How do I SELECT the first three posts, each with their comment count and first three comments?

Example: SQL Fiddle

Goal is to build a table like the one below

Expected output:

-- post_id   comment_id   user_id   body               created_at
-- 1         4            1         Hello. I'm Jane.   August, 28 2016 14:12:01
-- 1         1            2         Nice post, Jane.   August, 28 2016 14:12:01
-- 1         2            1         Thank you, John.   August, 28 2016 14:12:01
-- 1         3            2         You're welcome.    August, 28 2016 14:12:01
-- 2         2            1         This is post 2.    August, 28 2016 14:12:01
-- 2         5            2         I like this.       August, 28 2016 14:12:01
-- 2         6            1         Why, thank you.    August, 28 2016 14:12:01
-- 3         0            1         This is post 3.    August, 28 2016 14:12:01

Upvotes: 0

Views: 2057

Answers (3)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17177

Understanding the logic behind the expected output

You have a pretty sophisticated demand for your expected output. From what I understood from your sql fiddle looking at expected result, you want to:

  1. get first three posts
  2. get first three comments for them
  3. append the result from 1 and 2 with different logic for columns comment_id and body

The difference in logic seems to be like below:

For each row representing a post:

  • in comment_id store number of comments for that post
  • in user_id store the user that wrote a post
  • in body store the body of the post
  • in created_at store the timestamp when the post was created

While for each row representing comment the logic is analogical (but for a comment, not a post) with the exception of comment_id column where you want to store the comment id.

Query and explanation

For live example look at SQL fiddle

First, take first three posts and build rows for them counting comments for each one. Then, union those posts rows with comment rows, and use row_number() function to limit comment rows in the output to maximum of 3 per a post.

Assigning 0 as row number for posts means they fulfill the condition of rn <= 3.

To order the output as you wish so that for every post their comments are sorted right after them I've added an order_column to be able to include it in ORDER BY.

WITH first_posts AS (
    SELECT p.id AS post_id, COUNT(c.id) AS comment_id, p.user_id, p.body, p.created_at
    FROM (SELECT * FROM posts ORDER BY id LIMIT 3) AS p
    LEFT JOIN comments AS c
    ON p.id = c.post_id
    GROUP BY 1, 3, 4, 5
)
SELECT post_id, comment_id, user_id, body, created_at
FROM (
    SELECT 1 AS type, post_id, comment_id, user_id, body, created_at, 0 AS r
    FROM first_posts
    UNION ALL
    SELECT 2 AS type, p.post_id, c.id, c.user_id, c.body, c.created_at, 
        ROW_NUMBER() OVER (PARTITION BY p.post_id ORDER BY c.id) AS r
    FROM first_posts AS p
    INNER JOIN comments AS c
    ON p.post_id = c.post_id
    ORDER BY post_id, type, comment_id
) AS f
WHERE r <= 3;

Upvotes: 1

Charles Bretana
Charles Bretana

Reputation: 146541

edited Syntax and column names:

Select id, name, p.*, c.* 
from users u
    join posts p on p.User_Id = u.Id
       and (Select count(*) From posts 
            where user_Id = u.Id
               and created_at <= p.created_at) <= 3
    join comments c on c.post_Id = p.Id
       and (Select count(*) From comments 
            where post_Id = p.Id 
               and created_at <= c.created_at) <= 3

Upvotes: 1

FuzzyTree
FuzzyTree

Reputation: 32402

You can use a subquery to limit your selection to the first 3 posts and row_number to only include the first 3 comments per post:

SELECT * FROM (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY c.post_id ORDER BY c.id) rn
    FROM posts p
    JOIN comments c ON c.post_id = p.id
    WHERE p.id IN (SELECT id FROM posts ORDER BY id LIMIT 3)
) t WHERE rn <= 3

or if you want the first 3 posts & comments per user

SELECT * FROM (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY c.post_id ORDER BY c.id) comments_rn,
        ROW_NUMBER() OVER (PARTITION BY p.user_id ORDER BY p.id) post_rn        
    FROM posts p
    JOIN comments c ON c.post_id = p.id
    JOIN users u ON u.id = p.user_id
) t WHERE comments_rn <= 3 and post_rn <= 3

Upvotes: 1

Related Questions