SQL - Select semi-duplicate rows?

[walkermat]

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

[huangzhi]

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

[dwyaris]

@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