Troubleshooting
Problem
Removing rows that have columns with duplicate values
Resolving The Problem
The following SQL template can be used to remove rows that have a column with duplicate values. It will remove all other rows and leave one unique row. If the row has a column with a date value, it will leave the row with last inserted date.
However, if you want to remove the row with the duplicate column value, remove the line <COLUMN with last inserted date> desc from the sub select statement.
- INSERT INTO TABLE2
select
<column list>
from (
SELECT row_number () over (partition by <COLUMN with duplicate value> order by <COLUMN with duplicate value>, <COLUMN with last inserted date> desc) as r,
*
FROM TABLE1
) t where r = 1
;
[{"Product":{"code":"SSULQD","label":"IBM PureData System"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":null,"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"1.0.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Historical Number
NZ759804
Was this topic helpful?
Document Information
More support for:
IBM PureData System
Software version:
1.0.0
Document number:
462109
Modified date:
17 October 2019
UID
swg21569780