Duplicates in Oracle SQL

Question

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

you are going to need to use an analytic function like row_number() with some CASE logic to do that. — OldProgrammer
– OldProgrammer, Commented Aug 15, 2022 at 15:26
While this is possible in SQL, better deal with display issues in your GUI layer, i.e. your app or Website. — Thorsten Kettner
– Thorsten Kettner, Commented Aug 15, 2022 at 20:58

Matthew McPeak · Accepted Answer · 2022-08-15 15:50:13Z

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)

Perfect, I was so close to it but my syntax was a bit out. Your example was very easy to follow. Thanks Matthew.

Collectives™ on Stack Overflow

Duplicates in Oracle SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related