How do I sort on a group, compare to simalar goup and keep/delete based on compare?

P19_3141
P19_3141 used Ask the Experts™
on
I have a list with colums (CD,E,PA,ID,OtherStuff).

CD      E                  PA              ID       OtherStuff…       ** COMMENTS **
1957  570506946  570513928  1   570513929   || KEEP 1 RECORD
2189  416693913  416719434  2   416719435   || DELETE, NO MATCHING 1 RECORD
2648  551469941  551471415  1   551471417   || KEEP 1 RECORD
2725  567900760  570162977  2   570162978   || DELETE, NO MATCHING 1 RECORD
2760  454782146  454786664  2   454786665   || DELETE, NO MATCHING 1 RECORD
2796  448850126  449051008  1   449051009   || KEEP 1 RECORD
2796  448850126  449051008  1   459297246   || KEEP 1 RECORD
2844  413916336  413927406  2   413927407   || DELETE, NO MATCHING 1 RECORD
2866  416394126  416394371  1   416394372   || KEEP 1 RECORD
2894  501132355  501132371  1   501132372   || KEEP 1 RECORD
2894  501132355  501132371  2   502649167   || KEEP 2, MATCHING 1 RECORD
2894  501132355  501132371  2   511579670   || KEEP 2, MATCHING 1 RECORD
3263  453280624  474934153  2   474934154   || DELETE, NO MATCHING 1 RECORD
3366  437080073  438937550  1   438937551   || KEEP 1 RECORD
3366  437080073  438937550  2   438939285   || KEEP 2, MATCHING 1 RECORD
3533  409326447  417787354  2   417787355   || DELETE, NO MATCHING 1 RECORD
3533  422362233  422697017  2   422782034   || DELETE, NO MATCHING 1 RECORD
3869  444607762  444611280  1   444611281   || KEEP 1 RECORD
3869  444607762  444611280  2   446776942   || KEEP 2, MATCHING 1 RECORD
3869  451185892  458929969  2   458929972   || KEEP 2, MATCHING 1 RECORD

Like to group (CD,E,PA) such that if each row would either be:

(CD,E,PA),ID'1'
-OR-
(CD,E,PA),ID'2'

Then would like to KEEP the row if (comments added to table above):
(CD,E,PA),ID'1'
-OR-
(CD,E,PA),ID'2' has a matching (cd,e,pa) with id'1'

Also DELETE row if;
(CD,E,PA) with ID'2' DOES NOT had a matching  (CD,E,PA) with ID'1'

Thanks in advace for you thoughts.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
HainKurtSr. System Analyst

Commented:
select CD,E,PA from mytable
where ID in (1,2)
group by CD,E,PA

you cannot group by 3 columns and get the 4th or 5th column seperately :)
Sr. System Analyst
Commented:
maybe you want this

with
t1 as (select * from myTable where id=1),
t2 as (select * from myTable where id=2)
delete from t2 where not exists (select 1 from t1 where t1.cd=t2.cd and t1.e=t2.e and t1.pa=t2.pa)

Author

Commented:
I understand the 'with' solution and it worked using a small database on my personal PC.  Unfortunately,  I don't have database write privileges for the 'delete' at work so getting a permissions error on the work PC.   Any ideas as to how I can get around this?  

Author

Commented:
Solution worked in a TEST environment. Could not use in LIVE directory because don't have delete privilages.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial