title: Alternative to second-level nested correlated subquery
body: <p>I need an alternative to the following SQL query. The reason being that some earlier versions of Oracle database (prior to 12C, not sure which exactly) don't support the 2nd-level nested correlated subquery, where Oracle 12C does.</p>
  
  <pre><code>select 
      a.val1,
      a.val2,
      (
          select /* select the top single-field record from the inner select 
                  statement */
              * 
          from 
          (
              select 
                  b.val1
              from 
                  tb2 b
              where 
                  b.val2 &lt; a.val3 and b.val3 = a.val4
              order by 
                  b.val2 desc /* order by b.val2 desc in order to allow 
                                  the outer select statement to obtain the 
                                  largest b.val2 value */
          ) 
          where rownum = 1
      ) as someName
  from
      tb1 a;
  </code></pre>