Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Query help needed

Re: SQL Query help needed

From: Arun Mathur <themathurs_at_gmail.com>
Date: 24 Jun 2005 07:44:05 -0700
Message-ID: <1119624245.838209.140390@g44g2000cwa.googlegroups.com>


Use the row_number function as Rene suggests. See below for an example:

SQL> drop table aa;

Table dropped.

SQL> drop table bb;

Table dropped.

SQL> drop table cc;

Table dropped.

SQL> create table aa(item_code varchar2(5),   2 type number(1),
  3 rel_code varchar2(5));

Table created.

SQL> create table bb(rel_code varchar2(5),   2 item_code varchar2(5));

Table created.

SQL> insert into aa values ('1_01',1,null);

1 row created.

SQL> insert into aa values ('1_02',1,null);

1 row created.

SQL> insert into aa values ('1_03',1,null);

1 row created.

SQL> insert into aa values ('1_04',1,null);

1 row created.

SQL> insert into aa values ('1_05',1,null);

1 row created.

SQL> insert into aa values ('1_06',1,null);

1 row created.

SQL> insert into aa values ('2_01',2,'abc');

1 row created.

SQL> insert into aa values ('2_02',2,'bcd');

1 row created.

SQL> insert into aa values ('2_03',2,'cde');

1 row created.

SQL> insert into bb values ('abc','1_01');

1 row created.

SQL> insert into bb values ('abc','1_02');

1 row created.

SQL> insert into bb values ('abc','1_04');

1 row created.

SQL> insert into bb values ('abc','1_06');

1 row created.

SQL> insert into bb values ('bcd','1_02');

1 row created.

SQL> insert into bb values ('bcd','1_05');

1 row created.

SQL> insert into bb values ('bcd','1_06');

1 row created.

SQL> insert into bb values ('cde','1_02');

1 row created.

SQL> insert into bb values ('cde','1_04');

1 row created.

SQL> create table cc as
  2 select bb.item_code item_code_1,aa.item_code item_code_2,   3 row_number() over (partition by bb.item_code order by aa.item_code) priority
  4 from bb,aa
  5 where bb.rel_code = aa.rel_code(+);

Table created.

SQL> select * from cc order by item_code_1,item_code_2;

ITEM_ ITEM_ PRIORITY

1_01 2_01 1

1_02 2_01 1

1_02 2_02 2

1_02 2_03 3

1_04 2_01 1

1_04 2_03 2

1_05 2_02 1

1_06 2_01 1

1_06 2_02 2

9 rows selected.

SQL> select * from v$version;

BANNER


Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production

PL/SQL Release 9.2.0.1.0 - Production

CORE 9.2.0.1.0 Production

TNS for Solaris: Version 9.2.0.1.0 - Production

NLSRTL Version 9.2.0.1.0 - Production         

Regards,
Arun Received on Fri Jun 24 2005 - 09:44:05 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US