Re: Ordered Top-N in a subquery

From: Charanjiv <ckalha_at_gmail.com>
Date: 21 Sep 2004 00:28:06 -0700
Message-ID: <4277f1bd.0409202328.5ec09925@posting.google.com>

This should work ... gives the highest rank for all persons ..

Charanjiv

SQL> create table person (id number,
  2 name varchar2(20),
  3 phone varchar2(20)) ;

Table created.

SQL> create table persontitle
  2 ( id number,
  3 title varchar2(20),
  4 rank number ) ;

Table created.

SQL> insert into person values (
  2 1, 'Tom', '11');

1 row created.

SQL>
SQL> insert into person values (
  2 2, 'Fredy', '12');

1 row created.

SQL>
SQL> insert into person values (
  2 3, 'Donald', '13');

1 row created.

SQL> 
SQL> 
SQL> 
SQL> insert into persontitle values (

  2 1, 'Lion', 1) ;

1 row created.

SQL>
SQL> insert into persontitle values (
  2 1, 'Cub', 4) ;

1 row created.

SQL>
SQL> insert into persontitle values (
  2 2, 'Tiger', 2) ;

1 row created.

SQL>
SQL> insert into persontitle values (
  2 3, 'Bear', 3) ;

1 row created.

SQL> select * from
  2 (
  3 select p.ID, p.NAME, p.PHONE, pt.title ,

  4           RANK() OVER (PARTITION BY pt.id ORDER BY pt.rank  ASC) rnk
  5      from person p , persontitle pt
  6     where p.id = pt.id

  7 )
  8 Where rnk = 1
  9 /

        ID NAME                 PHONE                TITLE       RNK
---------- --------- -------------------- -------------------- ----------
         1 Tom                  11          Lion                 1
         2 Fredy                12          Tiger                1
         3 Donald               13          Bear                 1

jhtodd_at_uiuc.edu (Jeremy Todd) wrote in message news:<103a9d1.0409200802.12000bcf_at_posting.google.com>...
> 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 Tue Sep 21 2004 - 02:28:06 CDT