title: How to pass table alias to subsubquery in SQL?
body: <p>I've been struggling with <code>SQL</code> for a while now. I have to build a query to return a second highest id from a table. Sounds simple, especially if you've found <a href="http://www.orafaq.com/faq/how_does_one_select_the_top_n_rows_from_a_table" rel="nofollow">that link</a>.</p>
  
  <p>But I have a bit more complicated query:</p>
  
  <pre><code>SELECT 
    p.column1,
    p.column2,
    p.column3,
    p.column4,
    (
      SELECT 
        p.column1 
      FROM 
        p_table p
      WHERE 
        p.column2 = (
          SELECT
            id
          FROM (
            SELECT 
              id,
              RANK() OVER (ORDER BY id DESC) rank_id
            FROM 
              other_table 
            WHERE 
              field = p.column3
          )
          WHERE
            rank_id = 2
        )
      ) AS sth
  FROM ...
  </code></pre>
  
  <p>Oracle says <a href="http://ora-00904.ora-code.com/" rel="nofollow">ORA-00904: Unknown identifier</a> at <code>p.column3</code>.
  I understand why it says so - it doesn't recognize <code>p</code> as table name alias. </p>
  
  <p>Is it possible to pass it to nested query?</p>