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!
@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