SQL - Select semi-duplicate rows?

Expand Post »

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

walkermat

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Sep 2008

Permalink

Sep 6th, 2008

Re: SQL - Select semi-duplicate rows?

Try this code below:

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

huangzhi

Reputation Points: 10

Solved Threads: 13

Light Poster

Offline

48 posts
since Feb 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: SQL2005 Installation

Next Thread in MS SQL Forum Timeline: Sort Null values at top

DaniWeb Message

Cancel Changes

User Name
Password