sql - Oracle - select records in which a subset does not repeat -

sql - Oracle - select records in which a subset does not repeat -

i have oracle info set next columns f1, f2, f3, f4 follows:

a, b, c, d a, b, c, e a, f, c, d a, g, c, d

i filter out duplicate fields in column f1 , f2 only. illustration above, see line 1 , 2 have identical values (a,b) in field (f1,f2), need either

a, b, c, d or a, b, c, e

but not both. final result expect be:

a, b, c, d a, f, c, d a, g, c, d

or

a, b, c, e a, f, c, d a, g, c, d

how issue oracle statement accomplish goal? have tried:

select * t (rowid,f1,f2) in (select distinct rowid, f1,f2 t)

but statement not help , still print out. please help.

below quick , dirty script create testing info set:

create table "t" ( "f1" varchar2(20 byte), "f2" varchar2(20 byte), "f3" varchar2(20 byte), "f4" varchar2(20 byte) ) insert t (f1,f2,f3,f4) values ('a','b','c','d'); insert t (f1,f2,f3,f4) values ('a','b','c','e'); insert t (f1,f2,f3,f4) values ('a','f','c','d'); insert t (f1,f2,f3,f4) values ('a','g','c','h');

is corresponding needs:

select t.* t bring together (select f1, f2, min(rowid) rid t grouping by(f1,f2)) o on t.rowid = o.rid

see http://sqlfiddle.com/#!4/dcf9c/4

the inner query remove duplicated on f1,f2 (deterministically keeping minimum rowid in case of duplicates). outer select simple bring together on rowid extract entire row.

if t view, cannot utilize rowid. have rely on instead:

select f1, f2, f3, min(f4) f4 t natural bring together (select f1, f2, min(f3) f3 t grouping by(f1,f2)) o grouping by(f1,f2,f3);

see http://sqlfiddle.com/#!4/dcf9c/8

the key thought here create 3-uple distinct f1,f2 , corresponding minimum f3 (inner query). extending 3-uple adding minimum f4 (outer query). can generalized n-uple nesting more queries.

sql oracle