Good Afternoon everyone,
I have a table with duplicates, and what I am trying to do is delete duplicates based on the ID column but keep the record where the reportnumber is the min length.
So here is my setup:
CREATE TABLE PERSON
(id number,
reportnumber varchar2(20));
INSERT INTO person (id, reportnumber) VALUES('123','SER0AYY');
INSERT INTO person (id, reportnumber) VALUES('123','SER0AYY3455');
INSERT INTO person (id, reportnumber) VALUES('444','S0CRL111');
INSERT INTO person (id, reportnumber) VALUES('444','S0CRL');
INSERT INTO person (id, reportnumber) VALUES('444','S0CRL33');
INSERT INTO person (id, reportnumber) VALUES('555','S0A3V55');
INSERT INTO person (id, reportnumber) VALUES('555','S0A3V');
PERSON
| id | reportnumber |
|---|
| 123 | SER0AYY |
| 123 | SER0 |
| 444 | S0CRL111 |
| 444 | S0CRL |
| 444 | S0CRL33 |
| 555 | S0A3V55 |
| 555 | S0A3V |
After the delete, I would like to keep the following data: (the reportnumber column should be the value with the minimum lenght perr id number)
I have the query for the duplicates:
select id, count(*)
from person
group by id
having count(*) >1;
Delete from person where rowid not in (
select max(rowid) from rowid group by id );
That works fine to delete duplicates randomly, but I do not know how to delete where the value of the reportnumber column is not the minimum length. Any help is appreciated. Thank you in advance.