I have a DB table “TRANS” with multiple rows in. But some of the rows have certain columns that contain duplicate entries and I’m trying to create a select query to show me the data that is duplicated.

AUTHIDRESP  REQAMT  ADDRESS_NAME    HPAN MSG_TYPE TRANS_TYPE
123456      88.70    London         1    1         3
874567      4.78     Paris          23   1         3
456274      1.65     Paris          3    1         3
112356      88.70    London         1    1         3
887456      1.65     Paris          3    2         3 
114568      4.78     Paris          23   2         3

I’d like the select query to show the output where the Amount and Name and HPAN match but also display the AUTHID as this will be a unique value and group them together by ‘HPAN’ so you can see each entry more easily

So the output would be in order like this:

AUTHIDRESP  REQAMT  ADDRESS_NAME    HPAN MSG_TYPE TRANS_TYPE
123456      88.70   London           1    1        3
112356      88.70   London           1    1        3
887456       1.65   Paris            3    1        3
456274       1.65   Paris            3    2        3
114568       4.78   Paris            23   1        3
874567       4.78   Paris            23   2        3

I have tried this query but it is not giving me the output I need as it appears to just give a long list of everything.

select AUTHIDRESP, HPAN, MSGTYPE, REQAMT, TRANS_TYPE, ADDRESS_NAME
from TRANS s
where 
1 < (select count(*) from TRANS i where i.HPAN = s.HPAN and i.REQAMT = s.REQAMT and i.ADDRESS_NAME = s.ADDRESS_NAME and i.TRANS_TYPE = s.TRANS_TYPE) 
and date = '20240815' 

Any help would be much appreciated thanks