Oracle to remove duplicate rows based on condition
I have data similar to the table below.
+------+----------+----------+
| 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 rows by removing 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 special type,
- if matching date2 exists for date1 then select that row and remove other date1
- if the corresponding date2 does not exist, save it
Similarly,
- if matching date1 exists for date2 then select that row and remove the other date2
- if the corresponding date1 does not exist, save it
Is it possible to write an oracle query for this? Can anyone help me to do this?
+3
source to share
1 answer
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
)
+1
source to share