CODEWITHSUNDEEP
sqlpostgresqlgroup-by
cola
cola

Reputation: 12466

postgresql sql statement: Can't get the desired latest/recent comments, need to display the distinct posts.title with the latest comments

\d comments

                                   Table "public.comments"
   Column   |          Type          |                       Modifiers                       
------------+------------------------+-------------------------------------------------------
 id         | integer                | not null default nextval('comments_id_seq'::regclass)
 post_id    | integer                | not null
 name       | character varying(255) | not null
 email      | character varying(255) | not null
 content    | character varying(500) | not null
 created_at | date                   | 
 updated_at | date                   | 
Indexes:
    "comments_pkey" PRIMARY KEY, btree (id)

For this statement

select post_id,created_at from comments order by created_at limit 5;

I got

 post_id | created_at 
---------+------------
       5 | 2011-07-11
       5 | 2011-07-11
       5 | 2011-07-11
       8 | 2011-07-11
       2 | 2011-07-17
(5 rows)

But i need the result like this

 post_id | created_at 
---------+------------
       5 | 2011-07-11
       8 | 2011-07-11
       2 | 2011-07-17
(3 rows)

How can i rewrite the sql statement to get these three rows as result ?

\d posts

                                   Table "public.posts"
   Column    |          Type          |                     Modifiers                      
-------------+------------------------+----------------------------------------------------
 id          | integer                | not null default nextval('posts_id_seq'::regclass)
 title       | character varying(100) | not null
 content     | character varying(500) | not null
 created_at  | date                   | 
 updated_at  | date                   | 
 tags        | character varying(55)  | not null default '50'::character varying
 category_id | integer                | not null default 1
Indexes:
    "posts_pkey" PRIMARY KEY, btree (id)

With that three id from that three rows i need to get the posts.title from the posts table. How can i write the sql statement to get the posts.title with the comments.post_id = 5 or 8 or 2 ?

Upvotes: 0

Views: 126

Answers (1)

Joe Stefanelli
Joe Stefanelli

Reputation: 135848

EDIT: Modified answer based on comment.

 SELECT p.title, q.LatestCommentDate
     FROM (SELECT c.post_id, MAX(c.created_at) AS LatestCommentDate
               FROM comment c
               GROUP BY c.post_id) q
         INNER JOIN posts p
             ON q.post_id = p.id;

Upvotes: 1

Related Questions