title: Sub-select in oracle
body: <p>I'm trying to select the newest price from another table in a sub select. But I can't figure out how to get it working.</p>
  
  <p>This is what I've tried:</p>
  
  <pre><code>select something, somthingelse, 
    (
      select * from 
      (
        select QUOTE_PRICE as old_price
        from price_history
        where price_history.part_no= article_table.part_no
        order by valid_from desc
      ) where rownum=1
    )
  from  article_table where rownum &lt; 5
  </code></pre>
  
  <p>The subselect works by itself, but it can't find <code>article_table.part_no</code>:</p>
  
  <blockquote>
    <p>SQL Error: ORA-00904: "article_table "."part_no": invalid identifier</p>
  </blockquote>
  
  <p><strong>Update:</strong></p>
  
  <p>Current solution:</p>
  
  <pre><code>select something, somethingelse, (
    SELECT MIN(QUOTE_PRICE) KEEP (DENSE_RANK FIRST ORDER BY valid_from)
    FROM price_history
    WHERE part_no=article_table.part_no
  ) as old_price
  from  article_table a where rownum &lt; 5
  </code></pre>