EMP
      

      
ID   
      
Country   
      
Capital 
      
      

      
1
      
France
      
Paris
      
      

      
2
      
USA
      
Washington
      
      

      
3
      
France
      
Paris
      
      

      
4
      
India
      
New Delhi
      
      

      
5
      
USA
      
Washington
      
      
    

 ---- Step 1. Select the duplicate key values into a Temp holding table  
      SELECT    Country, Capital, col3=count(*)
      INTO      #holdkey
      FROM      EMP
      GROUP BY  Country, Capital
      HAVING    count(*) > 1
      
      
 ---- Step 2. Select the duplicate rows into a Temp holding table, eliminating duplicates in the process.  
      SELECT    DISTINCT EMP.*
      INTO      #holddups
      FROM      EMP, #holdkey
      WHERE     EMP.Country = #holdkey.Country
      AND       EMP.Capital = #holdkey.Capital
      
      
 ---- Step 3. Select the Rows from Temp Table  
      SELECT    Country, Capital, count(*)
      FROM      #holddups
      GROUP BY  Country, Capital
      
      
 ---- Step 4. Delete the duplicate rows from the original table.   
      DELETE  EMP
      FROM    EMP, #holdkey
      WHERE   EMP.Country = #holdkey.Country
      AND     EMP.Capital = #holdkey.Capital
      
      
 ---- Step 5. Put the unique rows back in the original table.   
      INSERT EMP SELECT * FROM #holddups
      
      
 ---- Step 6. Select rows from Table(without Duplicates)  
      SELECT * FROM EMP   
    

      

      
Output
      

      
Country   
      
Capital 
      
      

      
France
      
Paris
      
      

      
India
      
New Delhi
      
      

      
USA
      
Washington