Oracle nested correlated subquery problem

Question

Consider table1 and table2 with a one-to-many relationship (table1 is the master table and table2 is the detail table). I want to get records from table1 where some value ('XXX') is the value of the most recent record in table2 of the detail records correlated to table1. What I want to do is this:

select t1.pk_id
  from table1 t1
 where 'XXX' = (select a_col
                  from (  select a_col
                            from table2 t2
                           where t2.fk_id = t1.pk_id
                        order by t2.date_col desc)
                 where rownum = 1)

But, because the reference to table1 (t1) in the correlated subquery is two-levels deep, it pops up with an Oracle error (invalid id t1). I need to be able to rewrite this, but the one caveat is that only the where clause may be changed (i.e. the initial select and from must remain unchanged). Can it be done?

Answer 1

Here's a different analytic approach:

select t1.pk_id
  from table1 t1
 where 'XXX' = (select distinct first_value(t2.a_col)
                                  over (order by t2.date_col desc)
                  from table2 t2
                  where t2.fk_id = t1.pk_id)

And here's the same idea using a ranking function:

select t1.pk_id
  from table1 t1
 where 'XXX' = (select max(t2.a_col) keep
                          (dense_rank first order by t2.date_col desc)
                  from table2 t2
                  where t2.fk_id = t1.pk_id)