Re: Ordered Top-N in a subquery

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 20 Sep 2004 20:47:09 GMT
Message-ID: <slrnckugec.20s.rene.nyffenegger@zhnt60m34.netarchitects.com>

In article <103a9d1.0409200802.12000bcf_at_posting.google.com>, Jeremy Todd wrote:
> 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

create table persontitle (
id number,

  name   varchar2(20),
  phone  varchar2(20),
  title  varchar2(20),

rank number
);

insert into persontitle values (
1, 'Tom', '11', 'Mouse', 4);

insert into persontitle values (
2, 'Tom', '99', 'Cat', 2);

insert into persontitle values (
3, 'Fredy', '55', 'Fish', 5);

insert into persontitle values (
4, 'Donald', '33', 'Duck', 3);

select id, name, phone, title from
(select id, name, phone, title

     from persontitle
    order by rank desc
  )
where rownum = 1;

hth
Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/

Received on Mon Sep 20 2004 - 15:47:09 CDT