CODEWITHSUNDEEP
sqloracle-database
user1205746
user1205746

Reputation: 3364

Oracle - select records in which a subset does not repeat

I have an oracle data set with the following columns F1, F2, F3, F4 as follows:

A, B, C, D
A, B, C, E
A, F, C, D
A, G, C, D

I would like to filter out the duplicate fields in column F1 and F2 only. From the example above, I see line 1 and 2 have identical values (A,B) in Field (F1,F2), I need to get either 

A, B, C, D
or
A, B, C, E

but not both. So the final result I expect would 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 do I issue an Oracle statement to achieve my goal? I have tried:

SELECT * FROM T WHERE (ROWID,F1,F2) IN
(SELECT DISTINCT  ROWID, F1,F2 FROM T)

but the statement does not help and still print everything out. Please help.

Below is the quick and dirty script to create the testing data set:

  CREATE TABLE "T" 
   (    
    "F1" VARCHAR2(20 BYTE), 
    "F2" VARCHAR2(20 BYTE), 
    "F3" VARCHAR2(20 BYTE), 
    "F4" VARCHAR2(20 BYTE)
   ) 

Insert into T (F1,F2,F3,F4) values ('A','B','C','D');
Insert into T (F1,F2,F3,F4) values ('A','B','C','E');
Insert into T (F1,F2,F3,F4) values ('A','F','C','D');
Insert into T (F1,F2,F3,F4) values ('A','G','C','H');

Upvotes: 1

Views: 949

Answers (2)

Neerav Kumar
Neerav Kumar

Reputation: 84

Use this for better performance (since it avoids joins)

SELECT DISTINCT 
F1, 
F2, 
LAST_VALUE(F3) OVER (PARTITION BY F1, F2) AS F3, 
LAST_VALUE(F4) OVER (PARTITION BY F1, F2) AS F4
FROM T

Upvotes: 0

Sylvain Leroux
Sylvain Leroux

Reputation: 51980

Is something like that corresponding to your needs:

SELECT T.*
FROM T 
JOIN (SELECT F1, F2, MIN(ROWID) RID FROM T GROUP BY(F1,F2)) O
ON T.ROWID = O.RID

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

The inner query will remove duplicated on F1,F2 (deterministically keeping the minimum ROWID in case of duplicates). Then the outer select is a simple join on ROWID to extract the entire row.

If T is a view, you cannot use ROWID. So you will have to rely on something like that instead:

SELECT F1, F2, F3, MIN(F4) F4
FROM T 
NATURAL JOIN (SELECT F1, F2, MIN(F3) F3 FROM T GROUP BY(F1,F2)) O
GROUP BY(F1,F2,F3);

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

The key idea here is to create a 3-uple with distinct F1,F2 and the corresponding minimum F3 (inner query). Then extending that 3-uple by adding the minimum F4 (outer query). It can easily be generalized to N-uple by nesting more queries.

Upvotes: 2

Related Questions