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

If record has a combination of duplicate id, addr and Ind, then select the max(update_date) from that record.
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 |
+----+----------+-----+-------------+--------+

score 1 · Answer 1 · answered Mar 10 '21 at 09:59

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

score 0 · Answer 2 · answered Oct 12 '18 at 21:24

0

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)

answered Oct 12 '18 at 21:24

Claudiu Radu

11
6

Eduard Okhvat · Answer 3 · 2019-11-30T20:30:23.430

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

Michael Kutz · Answer 4 · 2020-06-05T22:40:47.097

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;

score -1 · Answer 5 · answered May 05 '20 at 01:10

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

Select unique records from multiple records based on max date

5 Answers5