oracle Filter duplicate column values

Question

Below is the sample data extract i have. And i wanted to delete the duplicate row (last one in this example) as below. I was wondering how can i easily fetch this without that extra record in select query

ID YEAR CNT VOLUME INT_VOLUME RATE INT_RATE GM GM_RCNT 545 2016 12 5508 5508 1604 1604 0.71 NULL 545 2017 5 1138 2731 824 1977 0.28 -50.42 545 2018 NULL NULL -45 2351 NULL NULL NULL 626 2016 12 679862 679862 252693 252693 0.63 NULL 626 2017 12 705365 705365 282498 282498 0.6 3.75 626 2018 12 707472 707472 291762 291762 0.59 0.3 626 2018 NULL NULL 711372 NULL 295186 NULL NULL --Filter such rows in select

In the above case we have year (2018) coming twice for 626 ID. and I wanted to retain the earlier record. perhaps min(rowid)? — stack user
– stack user, Commented May 22, 2019 at 1:58

Gordon Linoff · Accepted Answer · 2019-05-22 01:59:05Z

You can choose one year for each id using row_number():

select t.* from (select t.*, row_number() over (partition by id, year order by id) as seqnum from t ) t where seqnum = 1;

This chooses an arbitrary row to keep. You can adjust the order by to refine which row you want to keep. You can order by rowid, but there is no guarantee that it is the "earliest" row. You need a date or sequence column for that purpose.

Man no doubt you are GENIUS with 812k reputations..THANK YOU.

Collectives™ on Stack Overflow

oracle Filter duplicate column values

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related