1,105,423 Community Members

SQL - Select semi-duplicate rows?

Member Avatar
walkermat
Newbie Poster
2 posts since Sep 2008
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Daft subject header, but its early and brain not awake yet!

One for the SQL gurus (either that i've missed something here....lol)

I want to select rows where there are duplicates of data in some columns, but not others. Where a duplication IS found, i want to return all rows involved....

Table
A B C
1 w m
1 w n
1 x m
1 x n
2 x p
2 x q
2 y o
3 y r
3 z s
4 y t
5 z v


So i want to select:-
A B C
1 w m
1 w n
1 x m
1 x n
2 x p
2 x q

The selection contains all rows where there are duplicates of data in Column A and Column B. Reckon it could include a nested select statement perhaps with a 'Count > 1' in it somewhere; but just cant see it.....:confused:

Hoping its not too 'simple' and thanks for looking!

Mat

Member Avatar
huangzhi
Light Poster
48 posts since Feb 2008
Reputation Points: 0 [?]
Q&As Helped to Solve: 13 [?]
Skill Endorsements: 0 [?]
 
0
 

Try this code below:

select *
  from	#tmpX X inner join (select A, B from #tmpX group by A, B having count(*) > 1) Y on
	X.A = Y.A and X.B = Y.B
Member Avatar
dwyaris
Newbie Poster
1 post since Feb 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

@huangzhi
Thanks your query was a great help - spot on.

My problem was slightly different where part of the item code value in one column was being checked for duplicates - this is my query based on your advice that resolved the problem:

SELECT T0.[ItemCode], right(T0.[ItemCode],2) AS "Suffix", left(T0.[ItemCode],7) AS "ItemCodePrefix", T0.[ItemName] AS "ItemDescription"
INTO ##Temp1 FROM OITM T0
Select T1.[Suffix] AS "Item Code Suffix", T1.[ItemCodePrefix] AS "Item Code Prefix", T1.[ItemDescription] AS "Item Name", T1.[ItemCode] from ##Temp1 T1 INNER JOIN (Select T1.[ItemCodePrefix] from ##Temp1 T1 GROUP BY T1.[ItemCodePrefix] having count(*) > 1) T2 on T1.[ItemCodePrefix] = T2.[ItemCodePrefix]
Drop Table ##Temp1

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article