Oracle subquery does not see the variable from the outer block 2 levels up

Question

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 p.post_id,  (select * from   (select comment_body from comments where post_id = p.post_id   order by created_date asc) where rownum=1 ) the_first_comment FROM posts p   

and it works fine.

However, in Oracle I'm getting an error ORA-00904 p.post_id: invalid identifier.

It seems to work fine for one subselect, but I cannot get the comment with only one due to the fact that I need to use rownum (no limit / offset in Oracle).

What am I doing wrong here?

Answer 1

No, Oracle doesn't correlate the subqueries nested more than one level deep (and neither does MySQL).

This is a well-known problem.

Use this:

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 

Answer 2

If you need SQL that is platform-independent, this will work:

SELECT p.post_id      , c.comment_body   FROM posts p      , comments c  WHERE p.post_id = c.post_id    AND c.created_date IN        ( SELECT MIN(c2.created_date)            FROM comments c2           WHERE c2.post_id = p.post_id         ); 

But it assumes that (post_id, created_date) is the primary key of comments. If it isn't, you're going to get more than one line posts that have comments with the same created_date.

Also, it is likely to be slower than the solution that uses analytics, given by Quassnoi.