Translate TOP 1 correlated subquery from SQL Server to Oracle

Question

How do I perform a correlated subquery in Oracle that returns the first matching row based on an ORDER BY clause? I'm trying to translate a query from SQL Server that does this.

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. TOP 1 ... ORDER BY is proprietary to SQL Server and I'm struggling translating it to rownum.

Note: It has been pointed out that this particular query doesn't require TOP/LIMIT/rownum since it is semantically equivalent to use Min() 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.

Here's the SQL Server query (and the SqlFiddle for it):

SELECT
  D.StartDate,
  (
    SELECT TOP 1 E.EndDate
    FROM dbo.Dates E
    WHERE
      E.EndDate >= D.EndDate
      AND NOT EXISTS (
        SELECT *
        FROM dbo.Dates E2
        WHERE
          E.StartDate < E2.StartDate
          AND E.EndDate > 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 < D2.EndDate
      AND D.EndDate > D2.EndDate
  );

This is what I've tried. I'm stymied because I'm getting an error on the D.EndDate outer reference.

ORA-00904: "D"."ENDDATE": invalid identifier

But what's the problem? A correlated subquery in the SELECT clause should have access to all outer table data. I don't know where to go next. (And the SqlFiddle for this).

SELECT
  D.StartDate,
  (
    SELECT *
    FROM (
      SELECT E.EndDate
      FROM Dates E
      WHERE
        E.EndDate >= D.EndDate
        AND NOT EXISTS (
          SELECT *
          FROM Dates E2
          WHERE
            E.StartDate < E2.StartDate
            AND E.EndDate > 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 < D2.EndDate
      AND D.EndDate > D2.EndDate
  );

In [this article](http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1853075500346799932), Tom says: "ANSI SQL has table references (correlation names) scoped to just one level deep" (and I have no clue how to fix your issue, sorry). — Mat, Feb 11 '13 at 08:50
@Mat I thought about that but rejected it because SQL Server has no problem with deeper references. Thanks for the tip. I'll try thinking up a way around this. In fact I expect to try using a nested derived table. — ErikE, Feb 11 '13 at 09:37

score 1 · Accepted Answer · answered Feb 11 '13 at 10:05

I might be missing something, but can you not use MIN instead of TOP 1 ... ORDER BY, since you are ordering by EndDate, StartDate DESC, and only selecting EndDate, the start date is not relevant to the sort, it is only considered when you have 2 end dates that are the same, but since you are only selecting end date it doesn't matter which of the two (or more end dates) are used:

SELECT  D.StartDate,
        (   SELECT  MIN(E.EndDate)
            FROM    Dates E
            WHERE   E.EndDate >= D.EndDate
            AND     NOT EXISTS 
                    (   SELECT  1
                        FROM    Dates E2
                        WHERE   E.StartDate < E2.StartDate
                        AND     E.EndDate > E2.StartDate
                    )
        ) EndDate
FROM    Dates D
WHERE   NOT EXISTS 
        (   SELECT  1
            FROM    Dates D2
            WHERE   D.StartDate < D2.EndDate
            AND     D.EndDate > D2.EndDate
        );

Example on SQL Fiddle

You're absolutely right. I can only plead that it was past midnight... :) The issue of the StartDate for equal end dates did matter for the `NOT EXISTS` E2 portion. In fact, I'm still not sure I got it 100% right... but anyway, thanks for seeing through this particular problem. Any thoughts on how to accomplish a correlated subquery of this type in Oracle, in any case? — ErikE, Feb 11 '13 at 19:58
I can't do it without using analytical functions, although I have never really worked with Oracle so there could be tricks I am missing. This is as far as I got before giving up... http://sqlfiddle.com/#!4/2e766/40 — GarethD, Feb 11 '13 at 20:44

Translate TOP 1 correlated subquery from SQL Server to Oracle

1 Answers1