Ordered Top-N in a subquery

I'm an MSSQL guy suddenly thrust into the Oracle world, so bear with me.

Suppose I need to get a simple personnel list consisting of name, phone, and the person's highest-ranked title (they can have more than one). The way it appears I should get the title is:

SELECT *
FROM (SELECT Title FROM PersonTitle WHERE ID = [x] ORDER BY Rank DESC) WHERE rownum = 1

So far, so good. The problem arises when I try to incorporate that into the main query:

SELECT
  ID,
  Name,
  Phone,   

  (SELECT *
   FROM
     (SELECT Title FROM PersonTitle WHERE ID = Person.ID ORDER BY Rank DESC)
   WHERE rownum = 1) "Title"
FROM Person

The error I get is '"Person"."ID": invalid identifier (#904).' I gather that the problem is that being embedded in a nested subquery severs its access to the fields of the top-level query.

Is there any way around this? This seems like such a common thing that I'm sure I must be overlooking something, but Google has been less than helpful. This is a data warehouse environment, so all I have is SELECT access; I can't create stored procedures or anything. Also, unfortunately for me, all the work must be done in one query (which will be passed to a third-party tool), so I can't construct the result set programmatically, either.

Thanks,
Jeremy Received on Mon Sep 20 2004 - 11:02:17 CDT