I am wanting to strip out these duplicates and to leave the occurence of the IP_ID which has the highest value in the PTYP_SVAL. For example, there are two rows in the file as follows.
Code: Select all
IP_ID, CDU_CIN, PTYP_SVAL
2, 5, 14
2, 5, 15
If it makes it easier, the data is being extracted from a DB2 table (SQL below). Is it possible to add something to the SQL to not select the unwanted rows ?
Code: Select all
SELECT DISTINCT
DWH_IP_XREF.IP_ID,
DWH_IP_XREF.CDU_CIN,
DWH_PTYP_STAT_INFO.PTYP_SVAL
FROM
#pUKDWSCHEMA#.DWH_IP_XREF AS DWH_IP_XREF
INNER JOIN
#pUKDWSCHEMA#.DWH_IP_ARRG_RELN AS DWH_IP_ARRG_RELN
ON DWH_IP_XREF.IP_ID = DWH_IP_ARRG_RELN.IP_ID
INNER JOIN
#pUKDWSCHEMA#.DWH_PRAR AS DWH_PRAR
ON DWH_IP_ARRG_RELN.ARRG_ID = DWH_PRAR.ARRG_ID
INNER JOIN
#pUKDWSCHEMA#.DWH_PTYP_STAT_INFO AS DWH_PTYP_STAT_INFO
ON DWH_PRAR.PTYP_ID = DWH_PTYP_STAT_INFO.PTYP_ID
INNER JOIN
#pUKDWSCHEMA#.DWH_ARRG AS DWH_ARRG
ON DWH_IP_ARRG_RELN.ARRG_ID = DWH_ARRG.ARRG_ID
WHERE
DWH_IP_XREF.CDU_CIN IS NOT NULL
AND
DWH_PRAR.PTYP_ID IS NOT NULL
AND
DWH_ARRG.ARRG_ENDT IS NULL
AND
DWH_PTYP_STAT_INFO.PTYP_STAT_TYCD = 'RANK'
AND
DWH_PTYP_STAT_INFO.PTYP_SVAL > 0
ORDER BY
DWH_IP_XREF.IP_ID,
DWH_PTYP_STAT_INFO.PTYP_SVAL;