Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Query help needed
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