How can I reference an outer table in a subquery for a self left join?

Question

With this schema:

CREATE TABLE BAR (id INT PRIMARY KEY);
CREATE TABLE FOO (id INT PRIMARY KEY, rank INT UNIQUE, fk INT, FOREIGN KEY (fk) REFERENCES Bar(id));

INSERT INTO BAR (id) VALUES (1);
INSERT INTO BAR (id) VALUES (2);

-- sample values
INSERT INTO FOO (id, rank, fk) VALUES (1, 10, 1);
INSERT INTO FOO (id, rank, fk) VALUES (2, 3, 1);
INSERT INTO FOO (id, rank, fk) VALUES (3, 9, 2);
INSERT INTO FOO (id, rank, fk) VALUES (4, 5, 1);
INSERT INTO FOO (id, rank, fk) VALUES (5, 12, 1);
INSERT INTO FOO (id, rank, fk) VALUES (6, 14, 2);

How can I query for certain rows of FOO and the rows linked to the same row of BAR with the next highest rank? That is, I want to search for certain rows ("targets"), and for each target row I also want to find another row ("secondary"), such that secondary has the highest rank of all rows with secondary.fk = target.fk and secondary.rank < target.rank.

For example, if I target all rows (no where clause), I would expect this result:

TARGET_ID  TARGET_RANK  SECONDARY_ID  SECONDARY_RANK
---------  -----------  ------------  --------------
        1           10             4               5
        2            3          NULL            NULL
        3            9          NULL            NULL
        4            5             2               3
        5           12             1              10
        6           14             3               9

When the target row has id 2 or 3, there is no secondary row because no row has the same fk as the target row and a lower rank.

I tried this:

SELECT F1.id AS TARGET_ID, F1.rank as TARGET_RANK, F2.id AS SECONDARY_ID, F2.rank AS SECONDARY_RANK
FROM FOO F1
LEFT JOIN FOO F2 ON F2.rank = (SELECT MAX(S.rank)
                               FROM FOO S
                               WHERE S.fk = F1.fk
                                 AND S.rank < F1.rank);

...but I got ORA-01799: a column may not be outer-joined to a subquery.

Next I tried this:

SELECT F1.id AS TARGET_ID, F1.rank AS TARGET_RANK, F2.id AS SECONDARY_ID, F2.rank AS SECONDARY_RANK
FROM FOO F1
LEFT JOIN (SELECT S1.rank, S1.fk
           FROM FOO S1
           WHERE S1.rank = (SELECT MAX(S2.rank)
                            FROM FOO S2
                            WHERE S2.rank < F1.rank
                              AND S2.fk = F1.fk)
           ) F2 ON F2.fk = F1.fk;

...but I got ORA-00904: "F1"."FK": invalid identifier.

Surely there's some way to do this in a single query?

Answer 1

It doesn't like the subquery inside the temporary table. The trick is to left join all the secondary rows with rank less than the target's rank, then use the WHERE clause to filter out all but the max while being sure not to filter out target rows with no secondary.

select F1.id as TARGET_ID, F1.rank as TARGET_RANK, F2.id as SECOND_ID, F2.rank as SECOND_RANK
from FOO F1
left join FOO F2 on F1.fk = F2.fk and F2.rank < F1.rank
where F2.rank is null or F2.rank = (select max(S.rank)
                                    from FOO S
                                    where S.fk = F1.fk
                                      and S.rank < F1.rank);