title: Translate TOP 1 correlated subquery from SQL Server to Oracle
body: <p>How do I perform a correlated subquery in Oracle that returns the first matching row based on an <code>ORDER BY</code> clause? I'm trying to translate a query from SQL Server that does this.</p>
  
  <p>For the record, I need to stick to (mostly) SQL-92 syntax. Analytic functions should not be used at all, and I need to minimize the use of nonstandard SQL. <code>TOP 1 ... ORDER BY</code> is proprietary to SQL Server and I'm struggling translating it to <code>rownum</code>.</p>
  
  <p>Note: It has been pointed out that this particular query doesn't require <code>TOP/LIMIT/rownum</code> since it is semantically equivalent to use <code>Min()</code> as we only want one column. But I would still appreciate, and will reward, any help on how to perform the translation as stated--since I would like to learn Oracle better.</p>
  
  <p>Here's the SQL Server query (and the <a href="http://sqlfiddle.com/#!3/7d679/5" rel="nofollow">SqlFiddle for it</a>):</p>
  
  <pre><code>SELECT
    D.StartDate,
    (
      SELECT TOP 1 E.EndDate
      FROM dbo.Dates E
      WHERE
        E.EndDate &gt;= D.EndDate
        AND NOT EXISTS (
          SELECT *
          FROM dbo.Dates E2
          WHERE
            E.StartDate &lt; E2.StartDate
            AND E.EndDate &gt; E2.StartDate
        )
      ORDER BY
        E.EndDate,
        E.StartDate DESC
    ) EndDate
  FROM
    dbo.Dates D
  WHERE
    NOT EXISTS (
      SELECT *
      FROM dbo.Dates D2
      WHERE
        D.StartDate &lt; D2.EndDate
        AND D.EndDate &gt; D2.EndDate
    );
  </code></pre>
  
  <p>This is what I've tried. I'm stymied because I'm getting an error on the <code>D.EndDate</code> outer reference.</p>
  
  <blockquote>
    <p>ORA-00904: "D"."ENDDATE": invalid identifier</p>
  </blockquote>
  
  <p>But what's the problem? A correlated subquery in the <code>SELECT</code> clause should have access to all outer table data. I don't know where to go next. (And <a href="http://sqlfiddle.com/#!4/2e766/6" rel="nofollow">the SqlFiddle for this</a>).</p>
  
  <pre><code>SELECT
    D.StartDate,
    (
      SELECT *
      FROM (
        SELECT E.EndDate
        FROM Dates E
        WHERE
          E.EndDate &gt;= D.EndDate
          AND NOT EXISTS (
            SELECT *
            FROM Dates E2
            WHERE
              E.StartDate &lt; E2.StartDate
              AND E.EndDate &gt; E2.StartDate
          )
        ORDER BY
          E.EndDate,
          E.StartDate DESC
      )
      WHERE rownum = 1
    ) EndDate
  FROM
    Dates D
  WHERE
    NOT EXISTS (
      SELECT *
      FROM Dates D2
      WHERE
        D.StartDate &lt; D2.EndDate
        AND D.EndDate &gt; D2.EndDate
    );
  </code></pre>