I'd like to get in one query a post and the first comment associated with the post. Here is how I do it in PostgreSQL:
(select * from
(select comment_body from comments where post_id = p.post_id
order by created_date asc) where rownum=1
FROM posts p
Oracle doesn't correlate the subqueries nested more than one level deep (and neither does
This is a well-known problem.
SELECT p.post_id, c.* FROM posts JOIN ( SELECT c.*, ROW_NUMBER() OVER (PARTITION BY post_id ORDER BY created_date ASC) AS rn FROM comments c ) c ON c.post_id = p.post_id AND rn = 1