CODEWITHSUNDEEP
sqloracle-databasejoin
Shanna
Shanna

Reputation: 783

oracle query to remove duplicate rows based on condition

I have data like the below table.

+------+----------+----------+
| type |  date1   |  date2   |
+------+----------+----------+
| IT1  | 05/01/15 | 08/01/15 |
| IT1  | 05/01/15 |          |
| IT1  | 04/01/15 |          |
| IT1  |          | 03/02/15 |
| IT1  | 06/01/15 | 03/02/15 |
| IT1  |          | 04/02/15 |
| IT2  | 05/01/15 |          |
| IT2  | 05/01/15 | 04/01/15 |
| IT2  | 03/01/15 |          |
| IT2  |          | 09/01/15 |
+------+----------+----------+

I need to get the rows by removing the duplicates, the result is below

+------+----------+----------+
| type |  date1   |  date2   |
+------+----------+----------+
| IT1  | 05/01/15 | 08/01/15 |
| IT1  | 04/01/15 |          |
| IT1  | 06/01/15 | 03/02/15 |
| IT1  |          | 04/02/15 |
| IT2  | 05/01/15 | 04/01/15 |
| IT2  | 03/01/15 |          |
| IT2  |          | 09/01/15 |
+------+----------+----------+

for a particaular type,

  1. if a corresponding date2 exists for a date1, then select that row and remove other date1
  2. if a corresponding date2 does not exists then keep it

Similarly,

  1. if a corresponding date1 exists for a date2, then select that row and remove other date2
  2. if a corresponding date1 does not exists then keep it

Is it possible to write a oracle query for this? Can anyone help me doing this ?

Upvotes: 3

Views: 509

Answers (1)

krokodilko
krokodilko

Reputation: 36087

Try this query:

select *
from table1 t1
WHERE
  "date2" IS NOT NULL
  AND
  "date1" IS NOT NULL
  OR
  "date2" IS NULL 
  AND NOT EXISTS (
    SELECT 1 FROM table1 t2
    WHERE t1."type" = t2."type"
      AND t1."date1" = t2."date1"
      AND t2."date2" IS NOT NULL
   )
   OR
  "date1" IS NULL 
  AND NOT EXISTS(
    SELECT 1 FROM table1 t2
    WHERE t1."type" = t2."type"
      AND t1."date2" = t2."date2"
      AND t2."date1" IS NOT NULL
   )

Demo: http://sqlfiddle.com/#!4/12b8b/8

Upvotes: 1

Related Questions