Home » SQL & PL/SQL » SQL & PL/SQL » remove duplicate rows in Hierarchical Queries (oracle 10g)
remove duplicate rows in Hierarchical Queries [message #283848] Wed, 28 November 2007 05:23 Go to next message
kang
Messages: 89
Registered: November 2007
Member
in the query below how to surpress last six rows(they are duplicate)?

drop table tbl_emp;

create table tbl_emp(
emp_no varchar2(20),
emp_name varchar2(20),
ref_emp_no varchar2(20)
);

insert into tbl_emp(emp_no,emp_name,ref_emp_no) values('677','cho','');
insert into tbl_emp(emp_no,emp_name,ref_emp_no) values('685','mun','');
insert into tbl_emp(emp_no,emp_name,ref_emp_no) values('812','bai','');
insert into tbl_emp(emp_no,emp_name,ref_emp_no) values('24','lee','');
insert into tbl_emp(emp_no,emp_name,ref_emp_no) values('25','ki','493');
insert into tbl_emp(emp_no,emp_name,ref_emp_no) values('26','kim','');
insert into tbl_emp(emp_no,emp_name,ref_emp_no) values('27','cho','506');
insert into tbl_emp(emp_no,emp_name,ref_emp_no) values('28','kang','430');
insert into tbl_emp(emp_no,emp_name,ref_emp_no) values('29','kim','');
insert into tbl_emp(emp_no,emp_name,ref_emp_no) values('30','park','');
insert into tbl_emp(emp_no,emp_name,ref_emp_no) values('31','park','420');
insert into tbl_emp(emp_no,emp_name,ref_emp_no) values('420','kim','812');
insert into tbl_emp(emp_no,emp_name,ref_emp_no) values('430','kang','');
insert into tbl_emp(emp_no,emp_name,ref_emp_no) values('493','mun','');
insert into tbl_emp(emp_no,emp_name,ref_emp_no) values('506','um','');

select level,emp_no,LPAD(' ', LEVEL * 3) ||emp_name,ref_emp_no from tbl_emp
connect by prior ref_emp_no = emp_no;

this gives
1 1 24 lee
2 1 25 ki 493
3 2 493 mun
4 1 26 kim
5 1 27 cho 506
6 2 506 um
7 1 28 kang 430
8 2 430 kang
9 1 29 kim
10 1 30 park
11 1 31 park 420
12 2 420 kim 812
13 3 812 bai
14 1 420 kim 812
15 2 812 bai
want to delete below rows
16 1 430 kang
17 1 493 mun
18 1 506 um
19 1 677 cho
20 1 685 mun
21 1 812 bai

Thanks.
Re: remove duplicate rows in Hierarchical Queries [message #283855 is a reply to message #283848] Wed, 28 November 2007 05:35 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

What do you want to do?

Kiran.
Re: remove duplicate rows in Hierarchical Queries [message #283856 is a reply to message #283848] Wed, 28 November 2007 05:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

Regards
Michel
Re: remove duplicate rows in Hierarchical Queries [message #284026 is a reply to message #283856] Wed, 28 November 2007 10:41 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
The rows you referenced are not duplicates. Since you omitted the START WITH clause, the result set contains a hierarchy for every row in the table. Take this hierarchy for example:

     LEVEL EMP_NO               EMP_NAME        REF_EMP_NO
---------- -------------------- --------------- -----------
...............................
         1 31                      park         420
         2 420                        kim       812
         3 812                           bai
...............................


EMP_NO 420 is repeated again at level 1
         1 420                     kim          812
         2 812                        bai


And EMP_NO 812 is repeated again at level 1
         1 812                     bai


I'm not sure what your overall objective is (yes Michel, I'm guessing Wink), but moving the PRIOR keyword in the CONNECT BY and adding a START WITH clause may help. Otherwise, it sounds like you want to suppress any rows that do not have any child rows, which could be done with a subquery.

Here is the result of moving the PRIOR keyword and adding a START WITH clause
select level
      ,emp_no
      ,LPAD(' ', LEVEL * 3) ||emp_name emp_name
      ,ref_emp_no from tbl_emp
start with ref_emp_no is null
connect by ref_emp_no = prior emp_no

     LEVEL EMP_NO               EMP_NAME        REF_EMP_NO
---------- -------------------- --------------- ----------
         1 677                     cho
         1 685                     mun
         1 812                     bai
         2 420                        kim       812
         3 31                            park   420
         1 24                      lee
         1 26                      kim
         1 29                      kim
         1 30                      park
         1 430                     kang
         2 28                         kang      430
         1 493                     mun
         2 25                         ki        493
         1 506                     um
         2 27                         cho       506
Re: remove duplicate rows in Hierarchical Queries [message #284099 is a reply to message #283855] Wed, 28 November 2007 19:31 Go to previous messageGo to next message
kang
Messages: 89
Registered: November 2007
Member
24 lee
25 ki 493
493 mun
26 kim
27 cho 506
506 um
28 kang 430
430 kang
29 kim
30 park
31 park 420
420 kim 812
812 bai
420 kim 812 X
812 bai X
430 kang X
493 mun X
506 um X
677 cho
685 mun
812 bai X

rows marked X should not appear because the emp_no is referenced by some rows.
Re: remove duplicate rows in Hierarchical Queries [message #284338 is a reply to message #283856] Thu, 29 November 2007 07:12 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Michel Cadot wrote on Wed, 28 November 2007 12:35

Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

Regards
Michel


Please, read it. Again.

MHE
Re: remove duplicate rows in Hierarchical Queries [message #284478 is a reply to message #284338] Thu, 29 November 2007 18:34 Go to previous messageGo to next message
kang
Messages: 89
Registered: November 2007
Member
I use oracle 10.2.0.1.0.

and this paraent-child column relationship is not familar. it is inverted I think.
Re: remove duplicate rows in Hierarchical Queries [message #284719 is a reply to message #284478] Fri, 30 November 2007 12:17 Go to previous message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
kang wrote on Thu, 29 November 2007 16:34

it is inverted I think.


Yep, that's what I said. Did you look at my earlier results?
Previous Topic: using a VARRAY inside a WHERE clause
Next Topic: no same values in same field
Goto Forum:
  


Current Time: Sat Dec 10 12:29:27 CST 2016

Total time taken to generate the page: 0.05327 seconds