Home > Net >  Duplicates in Oracle SQL
Duplicates in Oracle SQL

Time:08-18

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)

  • Related