How to remove duplicate rows from a join query in oracle?

Question

I have two tables bc_transactions & bc_messages. I used the below query to join these two tables

SELECT distinct(bt.USER_TRANS_ID),bm.TS,bm.STATUS
FROM bc_transactions bt
inner JOIN bc_messages bm
ON bt.USER_TRANS_ID=bm.USER_MESSAGE_ID
where bt.protocol_name  = 'Gateway'
and bt.STATUS=bm.STATUS
AND bt.startdate >=TRUNC(SYSDATE-2)
AND bt.startdate <=TRUNC(SYSDATE-1)
AND bt.STATUS like 'ERROR TRANSPORT'
AND bt.HOSTNAME='HEB'
order by bt.USER_TRANS_ID ASC;

the bc_messages table has multiple rows with status as ERROR TRANSPORT which have the same USER_MESSAGE_ID at different timestamp(bm.TS). I am trying to get only the latest row.

|USER_TRANS_ID |            TS        |   STATUS            |
-------------------------------------------------------------
| ID1          | 10-03-2020  15:01:23 |   ERROR TRANSPORT   |
| ID1          | 10-03-2020  15:15:23 |   ERROR TRANSPORT   |
| ID1          | 10-03-2020  15:30:23 |   ERROR TRANSPORT   |
| ID1          | 10-03-2020  15:35:23 |   ERROR TRANSPORT   |
| ID2          | 10-03-2020  16:10:23 |   ERROR TRANSPORT   |
| ID2          | 10-03-2020  16:11:23 |   ERROR TRANSPORT   |

[Fetch the row which has the Max value for a column](https://stackoverflow.com/q/121387/3404097) This is a faq. Before considering posting please read the manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. — philipxy, Mar 12 '20 at 09:13

score 1 · Accepted Answer · answered Mar 12 '20 at 09:10

You can use row_number() :

select t.*
from (select bt.USER_TRANS_ID, bm.TS,bm.STATUS,
             row_number() over (partition by bt.USER_TRANS_ID order by bm.TS desc) as seq
      from bc_transactions bt inner join
            bc_messages bm
            on bt.USER_TRANS_ID = bm.USER_MESSAGE_ID
       where bt.protocol_name  = 'Gateway' and 
             bt.STATUS=bm.STATUS and 
             bt.startdate >= TRUNC(SYSDATE-2) and 
             bt.startdate <= TRUNC(SYSDATE-1) and 
             bt.STATUS like 'ERROR TRANSPORT' and 
             bt.HOSTNAME = 'HEB'
     ) t
where seq = 1;

thanks a lot @Yogesh Sharma. Solved my issue – Nevin Thomas Mar 12 '20 at 09:14 — Nevin Thomas, Mar 12 '20 at 09:14

score 1 · Answer 2 · answered Mar 12 '20 at 09:11

You can use the ROW_NUMBER analytical function as following:

SELECT USER_TRANS_ID, TS, STATUS FROM 
(SELECT bt.USER_TRANS_ID,bm.TS,bm.STATUS, 
ROW_NUMBER() OVER (PARTITION BY bt.USER_TRANS_ID , bm.STATUS 
                   ORDER BY bm.TS DESC NULLS LAST) AS RN -- added this
FROM bc_transactions bt
inner JOIN bc_messages bm
ON bt.USER_TRANS_ID=bm.USER_MESSAGE_ID
where bt.protocol_name  = 'Gateway'
and bt.STATUS=bm.STATUS
AND bt.startdate >=TRUNC(SYSDATE-2)
AND bt.startdate <=TRUNC(SYSDATE-1)
AND bt.STATUS like 'ERROR TRANSPORT'
AND bt.HOSTNAME='HEB')
WHERE RN = 1 -- added this
order by bt.USER_TRANS_ID ASC;

Cheers!!

subin ashok · Answer 3 · 2021-12-06T05:57:20.363

take max of timestamp using group by

select
     bt.USER_TRANS_ID,max(bm.TS),bm.STATUS
 FROM
     bc_transactions bt
     inner JOIN bc_messages bm ON bt.USER_TRANS_ID=bm.USER_MESSAGE_ID
 where
     bt.protocol_name  = 'Gateway'
     and bt.STATUS=bm.STATUS<br>
     AND bt.startdate >=TRUNC(SYSDATE-2)
     AND bt.startdate <=TRUNC(SYSDATE-1)
     AND bt.STATUS like 'ERROR TRANSPORT' // its better to keep status without space
     AND bt.HOSTNAME='HEB'
 group by
     bt.USER_TRANS_ID,bm.STATUS
 order by bt.USER_TRANS_ID ASC;

How to remove duplicate rows from a join query in oracle?

3 Answers3