title: Oracle performance issue in getting first row in sub query
body: <p>I have a performance issue on the following (example) select statement that returns the first row using a sub query:</p>
  
  <pre><code>SELECT ITEM_NUMBER,
         PROJECT_NUMBER,       
         NVL((SELECT DISTINCT
                     FIRST_VALUE(L.LOCATION) OVER (ORDER BY L.SORT1, L.SORT2 DESC) LOCATION
              FROM   LOCATIONS L
              WHERE  L.ITEM_NUMBER=P.ITEM_NUMBER
                     AND L.PROJECT_NUMBER=P.PROJECT_NUMBER
             ),
            P.PROJECT_NUMBER) LOCATION
  FROM   PROJECT P
  </code></pre>
  
  <p>The <code>DISTINCT</code> is causing the performance issue by performing a <code>SORT</code> and <code>UNIQUE</code> but I can't figure out an alternative.</p>
  
  <p>I would however prefer something akin to the following but referencing within 2 select statements doesn't work:</p>
  
  <pre><code>SELECT ITEM_NUMBER,
         PROJECT_NUMBER,       
         NVL((SELECT LOCATION
              FROM   (SELECT L.LOCATION LOCATION
                             ROWNUM RN
                      FROM   LOCATIONS L
                      WHERE  L.ITEM_NUMBER=P.ITEM_NUMBER
                             AND L.PROJECT_NUMBER=P.PROJECT_NUMBER
                      ORDER BY L.SORT1, L.SORT2 DESC
                     ) R
              WHERE RN &lt;=1
             ), P.PROJECT_NUMBER) LOCATION
  FROM   PROJECT P
  </code></pre>
  
  <p>Additionally:
   - My permissions do not allow me to create a function.
   - I am cycling through 10k to 100k records in the main query.
   - The sub query could return 3 to 7 rows before limiting to 1 row.</p>
  
  <p>Any assistance in improving the performance is appreciated.</p>