I have a query that I want remove/supress duplicate values from.
I'm working with 3 tables
Table 1: AWARD
AWARD_ID | IDENTIFIER | AMOUNT |
---|---|---|
1001 | 200112 | 3000 |
1002 | 200113 | 4000 |
1003 | 200114 | 5000 |
Table 2:INSTALMENT_PAYMENT
INSTALMENT_ID | AWARD_ID | AMOUNT |
---|---|---|
1 | 1001 | 4000 |
2 | 1001 | -1000 |
3 | 1002 | 4000 |
4 | 1003 | 1000 |
5 | 1003 | 1000 |
6 | 1003 | 3000 |
Table 3:RECON
ID | REFERENCE | AMOUNT |
---|---|---|
1 | 200112 | 3000 |
2 | 200113 | 4000 |
3 | 200114 | 5000 |
So if I join TABLES 1 AND 2 For a simple query
`SELECT A.AWARD_ID, A.IDENTIFIER IP.AMOUNT, FROM AWARD A, INSTALMENT_PAYMENTS IP WHERE A.AWARD_ID=IP.AWARD_ID'
I get a return that looks like:
AWARD_ID | IDENTIFIER | INSTALMENT_ID | AMOUNT |
---|---|---|---|
1001 | 200112 | 1 | 4000 |
1001 | 200112 | 2 | -1000 |
1002 | 200113 | 3 | 4000 |
1003 | 200114 | 4 | 1000 |
1003 | 200114 | 5 | 1000 |
1003 | 200114 | 6 | 3000 |
If I add table 3 to the mix (for augments sake this data is supplied from 3rd party for reconciliation purposes and cannot be broken down to transaction level as in table 2)
SELECT A.AWARD_ID, A.IDENTIFIER IP.AMOUNT R.AMOUNT AS RECON_AMOUNT, FROM AWARD A, INSTALMENT_PAYMENTS IP, RECON R WHERE A.AWARD_ID=IP.AWARD_ID' AND A.IDENTIFER= R.REFERENCE
I get a return that looks like:
AWARD_ID | IDENTIFIER | INSTALMENT_ID | AMOUNT | RECON_AMOUNT |
---|---|---|---|---|
1001 | 200112 | 1 | 4000 | 3000 |
1001 | 200112 | 2 | -1000 | 3000 |
1002 | 200113 | 3 | 4000 | 4000 |
1003 | 200114 | 4 | 1000 | 5000 |
1003 | 200114 | 5 | 1000 | 5000 |
1003 | 200114 | 6 | 3000 | 5000 |
What I'm looking to do is suppress the identical values/duplicates in columns AWARD_ID, IDENTIFIER AND RECON_AMOUNT - if possible to look like:
AWARD_ID | IDENTIFIER | INSTALMENT_ID | AMOUNT | RECON_AMOUNT |
---|---|---|---|---|
1001 | 200112 | 1 | 4000 | 3000 |
2 | -1000 | |||
1002 | 200113 | 3 | 4000 | 4000 |
1003 | 200114 | 4 | 1000 | 5000 |
5 | 1000 | |||
6 | 3000 |
CodePudding user response:
Use CASE WHEN row_number() OVER ( PARTITION BY award_id ORDER BY instalment_id ) = 1 THEN award_id ELSE null END award_id
.. And similar variations for the other columns.
I don't have your data model, but here is a working example of the concept using DBA_OBJECTS
.
select CASE WHEN row_number() OVER ( PARTITION BY owner, object_type ORDER BY object_name, last_ddl_time) = 1 THEN owner ELSE NULL END owner,
CASE WHEN row_number() OVER ( PARTITION BY owner, object_type ORDER BY object_name, last_ddl_time) = 1 THEN object_type ELSE NULL END object_type,
object_name,
last_ddl_time
from dba_objects o
order by o.owner, o.object_type, o.object_name, o.last_ddl_time;
------- ------------- -------------------------- --------------- | OWNER | OBJECT_TYPE | OBJECT_NAME | LAST_DDL_TIME | ------- ------------- -------------------------- --------------- | ABM | INDEX | ABM_CURRS_PK | 12-DEC-21 | | | | ABM_MDLS_PK | 12-DEC-21 | | | | ABM_MLS_ACTS_N1 | 12-DEC-21 | | | | ABM_MLS_DS_PK | 12-DEC-21 | | | | ABM_MLS_PK | 12-DEC-21 | | | | ABM_MLS_RESOURCES_PK | 12-DEC-21 | | | | ABM_MLS_RES_PK | 12-DEC-21 | | | | ABM_MLS_RE_DS_PK | 12-DEC-21 | | | | ABM_RES_PK | 12-DEC-21 | | | | ABM_TAGS_PK | 12-DEC-21 | | | | ABM_TAG_SETS_PK | 12-DEC-21 | | ABM | TABLE | ABM_CURRS | 12-DEC-21 | | | | ABM_MDLS | 12-DEC-21 | | | | ABM_MLS | 12-DEC-21 | | | | ABM_MLS_ACTS | 12-DEC-21 | | | | ABM_MLS_DS | 12-DEC-21 | | | | ABM_MLS_RES | 12-DEC-21 | | | | ABM_MLS_RESOURCES | 12-DEC-21 | | | | ABM_MLS_RE_DS | 12-DEC-21 | | | | ABM_RES | 12-DEC-21 | | | | ABM_TAGS | 12-DEC-21 | | | | ABM_TAG_SETS | 12-DEC-21 | | ADDS | INDEX | SAS_ARIA_FEED_AUDIT_N1 | 12-DEC-21 | | | | SAS_ARIA_FEED_AUDIT_U1 | 12-DEC-21 | | | | SAS_ARIA_FEED_STAGING_N1 | 12-DEC-21 | ------- ------------- -------------------------- ---------------
Make sure you ORDER BY
your main query the same way that you ORDER BY
in the row_number()
window clause. Also, make sure to ORDER BY
using the table alias in your main query (e.g., ORDER BY a.award_id
instead of just ORDER BY award_id
)