SQL Join Tables - 2nd newest entry to related main entry in other table

Question

following abstract scenario. I have 2 tables. One is containing "posts" and the other "comments" If you create a new "post", there is also a new "comment" added of type "initial". Whenever you now add a real comment, it gets the postID and a type like "picture","text", "movie" ...

I now need to get one table showing the initial posts date and the first "real" comment. I want to know how long it took until the first comment was written.

SELECT post.ID,post.Title,post.Author,post.CreateDate,
comment.Type,comment.Text,comment.CreateDate
FROM Posts as post
INNER JOIN Comments as comments on post.ID = comment.PostID

That gives me the Post Info for the amount of comments. Now I want to just have the very first "real" comment date standing next to the createDate of the post ( where comment.Type != 'initial')

Don't know how to do that. Could someone help me in this case? It should be possible to run it also on MS SQL 2000...

Thanks in advance...

Answer 1

try this:

SELECT post.ID,max(post.Title),max(post.Author),min(post.CreateDate),
min(comment.CreateDate),
datediff(mi,min(post.CreateDate),min(comment.CreateDate)) as 'first comment in minutes'
FROM Posts as post
INNER JOIN Comments as comments on post.ID = comment.PostID
where  comment.Type != 'initial'
group by post.id