CODEWITHSUNDEEP
ibm-midrangedb2-400
Hassan Mojwed
Hassan Mojwed

Reputation: 37

Select distinct on 3 columns but return all columns

The following is a duplicate, because the trans code is the same.

I run this to remove such a dupe but its not doing:

SELECT DISTINCT * FROM rklib.clspaytpa
ORDER BY otord#, otusrn, ottrnc

So that in the following we only want 1 'RRF' row, and the CWA row.

OTORD#  OTTRND  OTTRT   OTUSRN            OTTRNC
05582907    20150518    173805  MMOLINA RRF
05582907    20150518    173810  MMOLINA RRF
05582907    20150519    181515  MMOLINA CWA

Upvotes: 0

Views: 64

Answers (1)

Mike Tunnicliffe
Mike Tunnicliffe

Reputation: 10772

This would keep otord#, ottrnd, otusrn distinct and collapse ottrt, ottrnc:

SELECT otord#, ottrnd, otusrn, MIN(ottrt) AS ottrt, MIN(ottrnc) AS ottrnc
FROM rklib.clspaytpa
GROUP BY otord#, ottrnd, otusrn

Upvotes: 2

Related Questions