Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Richard Branson
Yak Posting Veteran
84 Posts |
 Posted - 2004-06-10 : 09:50:25
|
| HiI've got a table with about 30 000 Distinct Records and about 10 fields. I've realised that there are some records which have the same information except for the Primary Key.How can I retrive all the records that have the same information?If Two records have the same information then it should retrieve both of themHelp?You can't teach an old mouse new clicks. |
|
|
JasonGoff
Posting Yak Master
158 Posts |
Posted - 2004-06-10 : 10:08:53
|
| SELECT A.* FROM MyTable AINNER JOIN (SELECT Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10 FROM MyTable GROUP BY Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10 HAVING Count(1) > 1) AS BON A.Col1=B.Col1 AND A.Col2=B.Col2 ....A.Col10=B.Col10That should do it. Probably a better way, but without seeing your tables not sure ! |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-06-10 : 10:15:56
|
| something in this manner.. i guess it can be simplified...select * from test T1 where TestId not in (select TestId from test T2 inner join test T1 on T2.Field1 = T1.Field1 and T2.Field2 = T1.Field2 and T2.Field3 = T1.Field3 and ... (columns to compare))Go with the flow & have fun! Else fight the flow :) |
|
|
|
|
|
|
|
|
Search for Match