OraFAQ Forum: SQL & PL/SQL » only show the duplicated records

Home » SQL & PL/SQL » SQL & PL/SQL » only show the duplicated records

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

only show the duplicated records [message #640730]

Tue, 04 August 2015 11:08

ajen7118
Messages: 3
Registered: July 2015

Junior Member

Hi,

I have a query below to show all the records with joining these two tables.

SELECT DISTINCT B.BF_ORGN_CD, B.LEV5, A.BF_ACTY_CD
FROM BF_ORGN A
INNER JOIN BF_ORGN_CNSL_TBL B
ON A.CD=B.BF_ORGN_CD
WHERE A.BF_ACTY_CD IS NOT NULL
ORDER BY B.BF_ORGN_CD,A.BF_ACTY_CD

My goal is only to show all the duplicate records. Can anyone help me on this? Thank you very much.

Bf_ORGN_CD LEV5 BF_ACTY_CD
AC_21234_2 AC_21200_1 402
AC_21236_2 AC_21200_1 402
AC_21238_2 AC_21200_1 402
AC_29000_1 AC_29000_1 802 ---> NOT SHOW (ONLY 1 RECORD)
AC_29988_1 AC_29988_1 801 ---> NOT SHOW (ONLY 1 RECORD)
AC_40040_1 AC_40040_1 201 ---> NOT SHOW (ONLY 1 RECORD)
AC_41061_1 CA_41061_1 207 ---> NOT SHOW (ONLY 1 RECORD)
AC_41080_1 AC_41080_1 207 ---> NOT SHOW (ONLY 1 RECORD)
AC_41196_1 AC_41196_1 207 ---> NOT SHOW (ONLY 1 RECORD)
AC_42404_1 AC_42404_1 801 ---> NOT SHOW (ONLY 1 RECORD)
AC_42405_1 AC_42405_1 801 ---> NOT SHOW (ONLY 1 RECORD)
AC_53980_1 AC_53980_1 207
AC_53982_2 AC_53980_1 207

Report message to a moderator

Re: only show the duplicated records [message #640731 is a reply to message #640730]

Tue, 04 August 2015 11:14

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.orafaq.com/forum/mv/msg/198103/640576/#msg_640576

Report message to a moderator

Re: only show the duplicated records [message #640733 is a reply to message #640731]

Tue, 04 August 2015 11:19

ajen7118
Messages: 3
Registered: July 2015

Junior Member

Thanks BlackSwan.

SELECT DISTINCT B.bf_orgn_cd, 
                B.lev5, 
                A.bf_acty_cd 
FROM   bf_orgn A 
       inner join bf_orgn_cnsl_tbl B 
               ON A.cd = B.bf_orgn_cd 
WHERE  A.bf_acty_cd IS NOT NULL 
ORDER  BY B.bf_orgn_cd, 
          A.bf_acty_cd

*BlackSwan formatted posted code using URL below
http://www.dpriver.com/pp/sqlformat.htm?ref=g_wangz

[Updated on: Tue, 04 August 2015 11:23] by Moderator

Report message to a moderator

Re: only show the duplicated records [message #640734 is a reply to message #640730]

Tue, 04 August 2015 11:21

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You have to:
1/ read OraFAQ Forum Guide
2/ read How to use [code] tags and make your code easier to read
3/ Explain what is a duplicate
4/ provide a [[test case]) with the result associated result for us to be able to work.

Report message to a moderator

Re: only show the duplicated records [message #640759 is a reply to message #640734]

Tue, 04 August 2015 15:53

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Can anyone help me on this?

consider using MINUS to remove the unique rows

Report message to a moderator

Previous Topic:	Create a temporary/transient table within a SELECT
Next Topic:	sql replacement price

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Aug 03 02:25:57 CDT 2026