CODEWITHSUNDEEP
sqlpostgresqljoingreatest-n-per-group
Robert C. Holland
Robert C. Holland

Reputation: 1813

How to SELECT post entries to which latest comment were created with SQL?

I have tables post and comment that has foreign key(post_id) to post. I want to get 100 "bump ordered" post entries. The post entry to which latest comment entry was created comes first. My first attempt was:

SELECT * FROM post WHERE id IN
(
    SELECT DISTINCT post_id FROM comment
    ORDER BY created_time DESC
    LIMIT 100
);

Returns: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

The 2nd attempt:

SELECT * FROM post WHERE id IN
(
    SELECT post_id from
    (SELECT DISTINCT(post_id), posted FROM comment) AS c
    ORDER BY c.created_time DESC
    LIMIT 100
);

No error this time but doesn't do what I want. How can I make SQL do what I want ?

Upvotes: 1

Views: 158

Answers (1)

GMB
GMB

Reputation: 222582

If you are looking to select the 100 posts that have the most recent comments, you could use aggregation :

select p.id, p.title, p.author
from posts p
inner join comments c on c.post_id = p.id
group by p.id, p.title, p.author
order by max(c.created_at) desc
limit 100

With this technique, you need to enumerate all posts columns that you want to see in the resultset in the select clause and in the group by clause.

Another option is to pre-aggregate: 

select p.*
from posts p
inner join (
    select post_id, max(created_at) max_created_at
    from comments 
    group by post_id 
    order by max(created_at) desc
    limit 100
) c on c.post_id = p.id
order by c.max_created_at desc

Upvotes: 1

Related Questions