Author |
Topic |
Johnph
Posting Yak Master
103 Posts |
Posted - 2014-06-17 : 15:06:57
|
I need to delete duplicates off a table. Duplicates are reliate on specific columns (COL1, COL2, COL3):TABLE1COL1,COL2,COL3,COl4,COL5A,A,A,B,BA,A,A,B,CA,A,B,B,BA,A,A,H,HB,B,B,F,FB,B,B,E,EFor table1, I want to get rid of a rows with matching COL1, COl2, and COl3. The results should be like this:TABLE1COL1,COL2,COL3,COl4,COL5A,A,A,B,BA,A,B,B,BB,B,B,F,F |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-06-17 : 15:16:20
|
Search here for "deleting duplicates". You'll be grouping on multiple columns.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
mmkrishna1919
Yak Posting Veteran
95 Posts |
Posted - 2014-06-18 : 03:34:16
|
Try this?with cte(col1,col2,col3,col4,col5,rn) as(select t1.*,row_number() over (partition by t1.col1,t1.col2,t1.col3 order by t1.col5 asc) rnfrom sqlteam t1inner join sqlteam t2 ont1.COL1 = t2.col1and t1.COL2 = t2.COL2and t1.COL3 = t2.COL3)select col1,col2,col3,col4,col5 into #testfrom cte where rn=1select * from #testM.MURALI kRISHNA |
|
|
|
|
|