CODEWITHSUNDEEP
sqloracle-databaseduplicates
Marcus
Marcus

Reputation: 3869

remove duplicate and take last occurence of column value in oracle sql

I have below query which is generating duplicate result for H_POSAL_ID as H_POSAL_ID can change H_KACCT_ID in time:

SELECT
  'XXX',
  'JP',
  ACC.H_KACCT_BK,
  LACC.H_KACCT_ID,
  HSAL.H_POSAL_BK,
  LACC.H_POSAL_ID
FROM DRD_ORD LACC
INNER JOIN TRC_ORD ACC
ON LACC.H_KACCT_ID   =ACC.H_KACCT_ID
AND LACC.SYS_DB_NAME =ACC.SYS_DB_NAME
AND ACC.SYS_DB_NAME    ='JP'
INNER JOIN H_SALES HSAL
ON LACC.H_POSAL_ID     = HSAL.H_POSAL_ID
AND LACC.SYS_DB_NAME   = HSAL.SYS_DB_NAME
AND HSAL.SYS_DB_NAME    ='JP'
WHERE LACC.SYS_DB_NAME ='JP'
and LACC.H_POSAL_ID = 302774;

Result:

enter image description here

I want to take only the last occurence of H_POSAL_ID vs H_KACCT_ID

Upvotes: 1

Views: 115

Answers (2)

Ponder Stibbons
Ponder Stibbons

Reputation: 14858

You could number rows in proper order using row_number() and take only first rows for each h_posal_id, like here:

demo

select h_kacct_bk, h_kacct_id, h_posal_bk, h_posal_id
  from (select h_kacct_bk, h_kacct_id, h_posal_bk, h_posal_id,
               row_number() over (partition by h_posal_id order by h_kacct_id desc)  rn
          from t)
  where rn = 1

You can also use max() keep dense rank... or last_value() or find max values of h_kacct_id for each h_posal_id in subquery. But row_number() seems to be more compact and readable.

Upvotes: 1

Gaurav
Gaurav

Reputation: 1109

Try below SQL , it contains additional condition in where clause which gives max H_KACCT_ID for each H_POSTAL_ID. There can be other way to write this by using a sub query instead of using additional condition in where.

SELECT
  'XXX',
  'JP',
  ACC.H_KACCT_BK,
  LACC.H_KACCT_ID,
  HSAL.H_POSAL_BK,
  LACC.H_POSAL_ID
FROM DRD_ORD LACC
INNER JOIN TRC_ORD ACC
ON LACC.H_KACCT_ID   =ACC.H_KACCT_ID
AND LACC.SYS_DB_NAME =ACC.SYS_DB_NAME
AND ACC.SYS_DB_NAME    ='JP'
INNER JOIN H_SALES HSAL
ON LACC.H_POSAL_ID     = HSAL.H_POSAL_ID
AND LACC.SYS_DB_NAME   = HSAL.SYS_DB_NAME
AND HSAL.SYS_DB_NAME    ='JP'
WHERE LACC.SYS_DB_NAME ='JP'
and (LACC.H_POSAL_ID, LACC.H_KACCT_ID) IN (SELECT H_POSAL_ID, MAX(H_KACCT_ID) H_KACCT_ID from DRD_ORD group by H_POSAL_ID)
and LACC.H_POSAL_ID = 302774;

Upvotes: 1

Related Questions