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 | +----+----------+-----+-------------+--------+

Calculate ROW_NUMBER() OVER (PARTITION BY id, addr, ind ORDER BY update_date DESC) in CTE and select only records where the calculated value is 1. — Akina
– Akina, Commented Oct 12, 2018 at 5:34
I think your description is not clear. Do you want the following: Divide the rows of a table into groups. A group should consist of all rows that have the same ID, ADDR and update_Date. From each group select an arbitrary row whose update_Date is equal to the maximal update_Date of its group. — miracle173
– miracle173, Commented May 5, 2020 at 5:09

Pantea · Accepted Answer · 2021-03-10 09:59:57Z

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

Claudiu Radu · Accepted Answer · 2018-10-12 21:24:55Z

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)

Eduard Okhvat · Accepted Answer · 2019-11-30 20:30:23Z

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 · Accepted Answer · 2020-06-05 22:40:47Z

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;

grego · Accepted Answer · 2020-05-05 01:10:27Z

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

no, that is not correct. this may select more than one record per group — miracle173
– miracle173, Commented May 5, 2020 at 5:13
Thanks, I'll work on it with an actual dataset. My GROUP BY clause is probably off here.. — grego
– grego, Commented May 6, 2020 at 16:21

Stack Exchange Network

Select unique records from multiple records based on max date

5 Answers 5

Hot Network Questions

Select unique records from multiple records based on max date

5 Answers 5

Related

Hot Network Questions