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 |
|
Gopher
Yak Posting Veteran
83 Posts |
 Posted - 2006-09-26 : 10:03:40
|
| Hi AllIf I have a table which contains duplicate data for exampleTable1Col1 Col2 Col3 Col4---- ---- ---- ----001 PGG 256 3256631 PGG 256 3256236 PGF 258 3258063 PGF 258 3258985 SGG 356 1116121 SGG 356 1116Col2, Col3, Col4 wil have the same data the only difference is the Col1 entry.Is there a statement I can use to identify which items are duplicate and what there numbers are? So I end up with a statement that will tell me what the duplicates.e.g001,631,PGG,256,3256Is this possible?Any help would be great - Thanks in advanceGopher |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-09-26 : 10:34:51
|
| Select * from tableWhere col1 in (select col1 from table group by col2,col3,col4 having count(*)>1)MadhivananFailing to plan is Planning to fail |
 |
|
|
Fromper
Starting Member
33 Posts |
Posted - 2006-09-26 : 11:05:06
|
quote:
Originally posted by madhivanan
Select * from tableWhere col1 in (select col1 from table group by col2,col3,col4 having count(*)>1)MadhivananFailing to plan is Planning to fail
??? This seems wrong to me. How can your subquery select col1 when you're grouping on other fields? I thought grouping has to be done only on selected fields.How about something like this?SELECT col1 FROM tableINNER JOIN (SELECT col2 as d2, col3 as d3, col4 as d4 FROM table GROUP BY col2, col3, col4 HAVING COUNT(*) > 1 )ON (col2 = d2 AND col3 = d3 AND col4 = d4)--Richard |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-09-26 : 12:15:48
|
| That should be min(col1)MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Selecting Duplicates question!