only show the duplicated records

Question

Post reply

only show the duplicated records

kennyhuang0108

Right there with Babe

Points: 733
More actions
August 4, 2015 at 10:23 am

#310532

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

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736183 More actions · Answer 1

count the records in the PK, group on that.

use a having clause with the count() > 1

Lowell SSC Guru Points: 323518 More actions · Answer 2

;WITH MyCTE([Bf_ORGN_CD],[LEV5],[BF_ACTY_CD])

AS

(

SELECT 'AC_21234_2','AC_21200_1','402' UNION ALL

SELECT 'AC_21236_2','AC_21200_1','402' UNION ALL

SELECT 'AC_21238_2','AC_21200_1','402' UNION ALL

SELECT 'AC_29000_1','AC_29000_1','802' UNION ALL

SELECT 'AC_29988_1','AC_29988_1','801' UNION ALL

SELECT 'AC_40040_1','AC_40040_1','201' UNION ALL

SELECT 'AC_41061_1','CA_41061_1','207' UNION ALL

SELECT 'AC_41080_1','AC_41080_1','207' UNION ALL

SELECT 'AC_41196_1','AC_41196_1','207' UNION ALL

SELECT 'AC_42404_1','AC_42404_1','801' UNION ALL

SELECT 'AC_42405_1','AC_42405_1','801' UNION ALL

SELECT 'AC_53980_1','AC_53980_1','207' UNION ALL

SELECT 'AC_53982_2','AC_53980_1','207'

)

SELECT [LEV5],COUNT(*) AS Cnt

FROM MyCTE

GROUP BY [LEV5]

HAVING COUNT(*) > 1;

--alternate to show all rows.

--select * from MyCTE WHERE [LEV5] IN(SELECT [LEV5] FROM MyCTE group by [LEV5] having count(*) > 1)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kennyhuang0108 Right there with Babe Points: 733 More actions · Answer 3

Thanks Steve.

When I added below part into my query

group by B.BF_ORGN_CD, B.LEV5, A.BF_ACTY_CD

having count(B.LEV5) >1

none of the records was showed. Not sure if I did something wrong.

thanks.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

kennyhuang0108 (8/4/2015)
Thanks Steve.
When I added below part into my query
group by B.BF_ORGN_CD, B.LEV5, A.BF_ACTY_CD
having count(B.LEV5) >1
none of the records was showed. Not sure if I did something wrong.
thanks.

Try using the group by that was posted in the previous example.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

kennyhuang0108 Right there with Babe Points: 733 More actions · Answer 5

Thank you all.

I think the result I would like to see is as below

sorry for the confusion.

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_53980_1 AC_53980_1 207

AC_53982_2 AC_53980_1 207

CB_10000_1CB_10000_1901

CB_10001_2CB_10000_1901

CB_10002_2CB_10000_1901

CB_10003_2CB_10000_1901

CB_10004_2CB_10000_1901

CB_10007_2CB_10000_1901

g.britton SSChampion Points: 13863 More actions · Answer 6

I used:

SELECT [LEV5],[BF_ACTY_CD], COUNT(*) as cnt

FROM MyCTE

GROUP BY [LEV5],[BF_ACTY_CD]

HAVING COUNT(*) > 1;

and got:

LEV5 BF_ACTY_CDcnt

AC_53980_1207 2

AC_21200_1402 3

Gerald Britton, Pluralsight courses

sgmunson SSC Guru Points: 110640 More actions · Answer 7

kennyhuang0108 (8/4/2015)
Thank you all.
I think the result I would like to see is as below
sorry for the confusion.
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_53980_1 AC_53980_1 207
AC_53982_2 AC_53980_1 207
CB_10000_1CB_10000_1901
CB_10001_2CB_10000_1901
CB_10002_2CB_10000_1901
CB_10003_2CB_10000_1901
CB_10004_2CB_10000_1901
CB_10007_2CB_10000_1901

Okay, I had to add to your sample data for those last 6 records:

WITH MyCTE AS (

SELECT 'AC_21234_2' AS Bf_ORGN_CD, 'AC_21200_1' AS LEV5, '402' AS BF_ACTY_CD UNION ALL

SELECT 'AC_21236_2', 'AC_21200_1', '402' UNION ALL

SELECT 'AC_21238_2', 'AC_21200_1', '402' UNION ALL

SELECT 'AC_29000_1', 'AC_29000_1', '802' UNION ALL

SELECT 'AC_29988_1', 'AC_29988_1', '801' UNION ALL

SELECT 'AC_40040_1', 'AC_40040_1', '201' UNION ALL

SELECT 'AC_41061_1', 'AC_41061_1', '207' UNION ALL

SELECT 'AC_41080_1', 'AC_41080_1', '207' UNION ALL

SELECT 'AC_41196_1', 'AC_41196_1', '207' UNION ALL

SELECT 'AC_42404_1', 'AC_42404_1', '801' UNION ALL

SELECT 'AC_42405_1', 'AC_42405_1', '801' UNION ALL

SELECT 'AC_53980_1', 'AC_53980_1', '207' UNION ALL

SELECT 'AC_53982_2', 'AC_53980_1', '207' UNION ALL

SELECT 'CB_10000_1', 'CB_10000_1', '901' UNION ALL

SELECT 'CB_10001_2', 'CB_10000_1', '901' UNION ALL

SELECT 'CB_10002_2', 'CB_10000_1', '901' UNION ALL

SELECT 'CB_10003_2', 'CB_10000_1', '901' UNION ALL

SELECT 'CB_10004_2', 'CB_10000_1', '901' UNION ALL

SELECT 'CB_10007_2', 'CB_10000_1', '901'

),

MyGROUPS AS (

SELECT LEV5

FROM MyCTE

GROUP BY LEV5

HAVING COUNT(*) > 1

)

SELECT MC.*

FROM MyCTE AS MC

INNER JOIN MyGROUPS AS MG

ON MC.LEV5 = MG.LEV5;

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

kennyhuang0108 Right there with Babe Points: 733 More actions · Answer 8

Thank you all very much.

Should I apply the whole thing or only below part in my query? thank you

SELECT LEV5

FROM MyCTE

GROUP BY LEV5

HAVING COUNT(*) > 1

)

SELECT MC.*

FROM MyCTE AS MC

INNER JOIN MyGROUPS AS MG

ON MC.LEV5 = MG.LEV5;

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

kennyhuang0108 (8/4/2015)
Thank you all very much.
Should I apply the whole thing or only below part in my query? thank you
SELECT LEV5
FROM MyCTE
GROUP BY LEV5
HAVING COUNT(*) > 1
)
SELECT MC.*
FROM MyCTE AS MC
INNER JOIN MyGROUPS AS MG
ON MC.LEV5 = MG.LEV5;

Which part of it actually achieves your desired results?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

kennyhuang0108 Right there with Babe Points: 733 More actions · Answer 10

Thank you very much Jason. None of the queries is working.

The assignment is to exclude single LEV5 data and only show all the duplicate LEV5.

thanks.

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_53980_1 AC_53980_1 207

AC_53982_2 AC_53980_1 207

CB_10000_1 CB_10000_1 901

CB_10001_2 CB_10000_1 901

CB_10002_2 CB_10000_1 901

CB_10003_2 CB_10000_1 901

CB_10004_2 CB_10000_1 901

CB_10007_2 CB_10000_1 901

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

kennyhuang0108 (8/4/2015)
Thank you very much Jason. None of the queries is working.
The assignment is to exclude single LEV5 data and only show all the duplicate LEV5.
thanks.
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_53980_1 AC_53980_1 207
AC_53982_2 AC_53980_1 207
CB_10000_1 CB_10000_1 901
CB_10001_2 CB_10000_1 901
CB_10002_2 CB_10000_1 901
CB_10003_2 CB_10000_1 901
CB_10004_2 CB_10000_1 901
CB_10007_2 CB_10000_1 901

Please provide an example of what exactly you want the resulting data to look like.

In addition to that, provide the exact queries that you are using that are not working for your requirements.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

alicesql SSCrazy Points: 2649 More actions · Answer 12

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

I have used the window function ROW_NUMBER in this situation with success. Maybe this would work for you (you may have to tidy up the syntax a bit)

WITH CTEx AS (SELECT DISTINCT B.BF_ORGN_CD

, B.LEV5

, A.BF_ACTY_CD

,rn = ROW_NUMBER( )

OVER ( PARTITION BY

B.BF_ORGN_CD

, B.LEV5

, A.BF_ACTY_CD

ORDER BY 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 )

SELECT *

FROM CTEx

WHERE rn>1

kennyhuang0108 Right there with Babe Points: 733 More actions · Answer 13

thank you very much everyone.

I was able to achieve that with all your generous helps.

thank you