Select unique records from multiple records based on max date

Question

I have table like below

+----+----------+-----+-------------+--------+
| ID |   ADDR   | IND | update_Date | Sq_NBR |
+----+----------+-----+-------------+--------+
|  1 | PHYSICAL | Y   | 11-Oct      |   1293 |
|  1 | BILL     | Y   | 10-Oct      |   1357 |
|  1 | BILL     | N   | 10-Sep      |  13567 |
|  1 | PHYSICAL | Y   | 10-Oct      |   8403 |
|  1 | BILL     | Y   | 6-Oct       |    135 |
|  2 | PHYSICAL | Y   | 9-Oct       |   5333 |
|  2 | BILL     | N   | 8-Aug       |    657 |
|  2 | BILL     | Y   | 8-Oct       |   3673 |
|  2 | BILL     | Y   | 10-Oct      |   2574 |
|  3 | BILL     | Y   | 11-Oct      |  57923 |
|  3 | BILL     | Y   | 11-Oct      |  29384 |
+----+----------+-----+-------------+--------+

need to check below multiple conditions before populating the data

1. If record has a combination of duplicate id, addr and Ind, then select the max(update_date) from that record.
2. If record has a combination of duplicate id, addr, Ind and update_Date, select only one record from the duplicate records (It can be any record).

Below was the expected result.

Can anyone plug me some ideas to achieve this.

here is the sqlfiddle: http://sqlfiddle.com/#!4/d0098/1

+----+----------+-----+-------------+--------+
| ID |   ADDR   | IND | Update_Date | Sq_NBR |
+----+----------+-----+-------------+--------+
|  1 | PHYSICAL | Y   | 11-Oct      |   1293 |
|  1 | BILL     | Y   | 10-Oct      |   1357 |
|  1 | BILL     | N   | 10-Sep      |  13567 |
|  2 | PHYSICAL | Y   | 9-Oct       |   5333 |
|  2 | BILL     | N   | 8-Aug       |    657 |
|  2 | BILL     | Y   | 10-Oct      |   2574 |
|  3 | BILL     | Y   | 11-Oct      |  29384 |
+----+----------+-----+-------------+--------+

Answer 1

Query below gives me the desired result :

SELECT  
       ID,
       ADDR,
       IND,
       UPDATE_DATE,
       SQ_NBR           
FROM (
SELECT ID,
       ADDR,
       IND,
       UPDATE_DATE,
       SQ_NBR,
       ROW_NUMBER() OVER (PARTITION BY ID , ADDR , IND ORDER BY UPDATE_DATE DESC) RMN
FROM TEST_TABLE ) A
WHERE RMN = 1

Answer 2

For the first condition

• count() Over (Partition by id, addr, Ind)
• max(update_date) Over (Partition by id, addr, Ind)

For the secontd condition

• row_number Over (Partition by id, addr, Ind and update_Date)

Answer 3

Inner "group by" solves the second condition of a question, and outer plus "max (sq_nbr) keep.." works for the first condition.

HR@mydb> select id, addr, ind, max(update_date),
  2         max (sq_nbr) keep (dense_rank last order by update_date) AS sq_nbr
  3    from
  4  (select id, addr, ind, update_date, min(sq_nbr) sq_nbr
  5    from orgin
  6   group by id, addr, ind, update_date)
  7   group by id, addr, ind
  8   order by id, addr desc
  9  /

    ID ADDR            I MAX(UPDATE_DATE)    SQ_NBR
---------- --------------- - ------------------- ----------
     1 PHYSICAL        Y 11.10.2018 00:00:00 1293
     1 BILL            N 10.09.2018 00:00:00 13567
     1 BILL            Y 10.10.2018 00:00:00 1357
     2 PHYSICAL        Y 19.10.2018 00:00:00 5333
     2 BILL            N 08.08.2018 00:00:00 657
     2 BILL            Y 10.10.2018 00:00:00 2574
     3 BILL            Y 11.10.2018 00:00:00 29384

7 rows selected.

The definition of the First function - max() over keep ... is here https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions065.htm#i1000901

Answer 4

LAST_VALUE() comes to mind. Documentation is here.

select
  last_value(id) over (partition by id,addr,ind order by update_date) as id,
  last_value(addr) over (partition by id,addr,ind order by update_date) as addr,
  last_value(ind) over (partition by id,addr,ind order by update_date) as ind,
  last_value(update_date) over (partition by id,addr,ind order by update_date) as update_date,
  last_value(sq_nbr) over (partition by id,addr,ind order by update_date) as sq_nbr
from orgin

But, if you upgrade to a current version of Oracle (12c+), you can use MATCH_RECOGNIZE statement. Example is here.

select a.*
from orgin
  MATCH_RECOGNIZE (
    partition by id,addr,ind
    order by update_date
    measures
      last( anyrow.update_date ) as update_date,
      last( anyrow.sq_nbr) as sq_nbr
    one row per match
    pattern (anyrow+)
    define
      anyrow as 1=1
    ) a;

Answer 5

I would suggest this:

select ID, ADDR, IND, Update_Date, Sq_NBR
from yourTable as yt
inner join
    (select max(Update_Date) as newestDate, ID, ADDR, IND
     from yourTable
     group by ID, ADDR, IND
    ) as newTable
on yt.ID = newTable.ID
and yt.ADDR = newTable.ADDR
and yt.IND = newTable.IND
and yt.Update_Date = newTable.newestDate
order by ID, ADDR