Remove Resultset Duplicates Based On Three Columns

Question

I have a complex oracle view which returns data which has logical duplicates within the rows returned. My goal is to retrieve only one row when duplicates are found based two columns (text and datetime), but to decide which one of the duplicates to return will be based on a third column (datetime).

I have simulated the result set below into a table with stubbed data (as found here on SQLFiddle):

CREATE TABLE TimeTable (
  ID number NOT NULL,
  NAME VARCHAR2(20) NOT NULL,      -- Grouped by this first
  TARGETVALUE INT,                 -- ultimate target value to be returned (no precedence from this value)
  NOTE VARCHAR2(20) NULL,          -- Just a note for the developer on StackOverflow
  BEGIN_DATE TIMESTAMP NOT NULL,   -- Grouped by this 2nd (down to the minute, not seconds) 
  APPROVAL_DATE TIMESTAMP NOT NULL -- Decides the ties for duplicates

 );

 insert into TimeTable (ID, NAME, TARGETVALUE, NOTE, BEGIN_DATE, APPROVAL_DATE) values 
(1, 'Alpha', 5,  'Duplicate First', '08-MAR-14 09.43.00.000000000', 
                                    '09-MAR-14 09.43.00.000000000');

 insert into TimeTable (ID, NAME, TARGETVALUE, NOTE, BEGIN_DATE, APPROVAL_DATE) values 
(2, 'Alpha', 2,  'Duplicate Middle', '08-MAR-14 09.43.00.000000000', 
                                     '09-MAR-14 09.43.00.000000000');


 insert into TimeTable (ID, NAME, TARGETVALUE, NOTE, BEGIN_DATE, APPROVAL_DATE) values 
(3, 'Alpha', 3, 'Final Target', '08-MAR-14 09.43.00.000000000', 
                                '09-MAR-14 10.00.00.000000000');

-- Same time as alpha, but not related.
 insert into TimeTable (ID, NAME, TARGETVALUE, NOTE, BEGIN_DATE, APPROVAL_DATE) values 
(4, 'Beta', 4, 'Only Target', '08-MAR-14 09.43.30.000000000', 
                              '09-MAR-14 11.00.30.000000000');

The result set which is needed would be 2 rows of

3, 'Alpha', 3, '08-MAR-14 09.43.00.000000000', '09-MAR-14 10.00.00.000000000'
4, 'Beta', 4, '08-MAR-14 09.43.30.000000000' '09-MAR-14 11.00.30.000000000'

Note for clarification if I had this value in the database

5, 'Alpha', 8, '09-MAR-14 09.43.00.000000000', '12-MAR-14 10.00.00.000000000'

Then that Alpha set would be unique and also returned because it is not considered a duplicate due to the different BEGIN_DATE (which is the 9th of March not 8th).

---

Here are the rules to follow

1. The NAME relates data.
2. The BEGIN_DATE is the second relation where exact times up to the minute will have duplicates which need to be weeded out to be based on #3.
3. If there are duplicates per #1 and #2, then they will be removed as determined by the latest APPROVAL_DATE which will win over the earlier dates.

Answer 1

It should be a simple implementation of ANALYTICS to aggregate the data based on the rules mentioned.

You want the MAX of APPROVAL DATE among each group of NAME, BEGIN_DATE. So, all you need to do is :

MAX(APPROVAL_DATE) OVER(PARTITION BY NAME, BEGIN_DATE ORDER BY APPROVAL_DATE DESC) max_appr_dt

And, in your outer query, just filter out the DUPLICATES using WHERE APPROVAL_DATE = max_aapr_dt in the PREDICATE.

Note From PERFORMANCE point of view, this approach will do a TABLE SCAN only once. Thus, much better than the other approach of joining the table and having multiple table scans

Update Adding complete test cases as requested in comments

There are two ways using analytic :

1.MAX

SQL> SELECT *
  2  FROM
  3    (SELECT A.*,
  4      MAX(APPROVAL_DATE) OVER(PARTITION BY NAME, BEGIN_DATE ORDER BY APPROVAL_DATE DESC) max_appr_dt
  5    FROM TIMETABLE A
  6    )
  7  WHERE approval_date = max_appr_dt
  8  /

        ID NAME                 TARGETVALUE NOTE                 BEGIN_DATE                     APPROVAL_DATE                  MAX_APPR_DT
---------- -------------------- ----------- -------------------- ------------------------------ ------------------------------ ------------------------------
         3 Alpha                          3 Final Target         08-MAR-14 09.43.00.000000 AM   09-MAR-14 10.00.00.000000 AM   09-MAR-14 10.00.00.000000 AM
         4 Beta                           4 Only Target          08-MAR-14 09.43.30.000000 AM   09-MAR-14 11.00.30.000000 AM   09-MAR-14 11.00.30.000000 AM

2.ROW_NUMBER()

SQL> SELECT *
  2  FROM
  3    (SELECT a.*,
  4      row_number() OVER(PARTITION BY NAME, BEGIN_DATE ORDER BY APPROVAL_DATE DESC) AS "RNK"
  5    FROM TIMETABLE A
  6    )
  7  WHERE rnk =1
  8  /

        ID NAME                 TARGETVALUE NOTE                 BEGIN_DATE                     APPROVAL_DATE                         RNK
---------- -------------------- ----------- -------------------- ------------------------------ ------------------------------ ----------
         3 Alpha                          3 Final Target         08-MAR-14 09.43.00.000000 AM   09-MAR-14 10.00.00.000000 AM            1
         4 Beta                           4 Only Target          08-MAR-14 09.43.30.000000 AM   09-MAR-14 11.00.30.000000 AM            1

Execution plans for both the queries :

SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3  FROM
  4    (SELECT A.*,
  5      MAX(APPROVAL_DATE) OVER(PARTITION BY NAME, BEGIN_DATE ORDER BY APPROVAL_DATE DESC) max_appr_dt
  6    FROM TIMETABLE A
  7    )
  8  WHERE approval_date = max_appr_dt
  9  /

Explained.

SQL>
SQL> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2691156688

---------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |     4 |   356 |     3   (0)| 00:00:01 |
|*  1 |  VIEW               |           |     4 |   356 |     3   (0)| 00:00:01 |
|   2 |   WINDOW SORT       |           |     4 |   304 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| TIMETABLE |     4 |   304 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("APPROVAL_DATE"="MAX_APPR_DT")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

19 rows selected.

SQL>
SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3  FROM
  4    (SELECT a.*,
  5      row_number() OVER(PARTITION BY NAME, BEGIN_DATE ORDER BY APPROVAL_DATE DESC) AS "RNK"
  6    FROM TIMETABLE A
  7    )
  8  WHERE rnk =1
  9  /

Explained.

SQL>
SQL> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3768566268

--------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |     4 |   356 |     3   (0)| 00:00:01 |
|*  1 |  VIEW                    |           |     4 |   356 |     3   (0)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|           |     4 |   304 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | TIMETABLE |     4 |   304 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RNK"=1)
   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "NAME","BEGIN_DATE" ORDER BY
              INTERNAL_FUNCTION("APPROVAL_DATE") DESC )<=1)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

21 rows selected.

Answer 2

I know that you are using Oracle DB. But, I tested this using SQL server. The SQL should work for all DBs. Try my query though. I am not sure if this is the most efficient way to do it. Let me know if this helps.

select t.ID, t.name, t.targetvalue, t.begin_date, t.approval_date
from
(
select name, begin_date, max(approval_date) as approval_date
from timetable
group by name, begin_date
) as mx
inner join timetable as t
on mx.name = t.name and
mx.begin_date = t.begin_date and
mx.approval_date = t.approval_date

Extra query - if you want to create the table in the question inside SQL server -

CREATE TABLE TimeTable (
  ID int NOT NULL,
  NAME VARCHAR(20) NOT NULL,      
  TARGETVALUE INT,                
  NOTE VARCHAR(20) NULL,          
  BEGIN_DATE datetime NOT NULL,  
  APPROVAL_DATE datetime NOT NULL 

 );

 insert into TimeTable (ID, NAME, TARGETVALUE, NOTE, BEGIN_DATE, APPROVAL_DATE) values 
(1, 'Alpha', 5,  'Duplicate First', '08-03-14 09:43:00', 
                                    '09-03-14 09:43:00');

 insert into TimeTable (ID, NAME, TARGETVALUE, NOTE, BEGIN_DATE, APPROVAL_DATE) values 
(2, 'Alpha', 2,  'Duplicate Middle', '08-03-14 09:43:00', 
                                     '09-03-14 09:43:00');


 insert into TimeTable (ID, NAME, TARGETVALUE, NOTE, BEGIN_DATE, APPROVAL_DATE) values 
(3, 'Alpha', 3, 'Final Target', '08-03-14 09:43:00', 
                                '09-03-14 10:00:00');

-- Same time as alpha, but not related:
 insert into TimeTable (ID, NAME, TARGETVALUE, NOTE, BEGIN_DATE, APPROVAL_DATE) values 
(4, 'Beta', 4, 'Only Target', '08-03-14 09:43:30', 
                              '09-03-14 11:00:30');