MS SQL

MS SQL RSS

SQL - Select semi-duplicate rows?

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Sep 2008

Posts: 2

Reputation: walkermat is an unknown quantity at this point

Solved Threads: 0

walkermat

Offline

Newbie Poster

SQL - Select semi-duplicate rows?

Sep 2nd, 2008

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.....

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

Mat

Last edited by walkermat; Sep 2nd, 2008 at 7:51 pm. Reason: Added some more words....

•

Join Date: Feb 2008

Posts: 42

Reputation: huangzhi is an unknown quantity at this point

Solved Threads: 13

huangzhi

Offline

Light Poster

Re: SQL - Select semi-duplicate rows?

Sep 6th, 2008

Try this code below:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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