Learn T-SQL Commands with Samples
Skip Navigation Links
Skip Navigation Links.
Expand DatabaseDatabase
Expand TableTable
Expand ViewView
Expand Stored ProcedureStored Procedure
Expand Data FilteringData Filtering
Expand Data GroupingData Grouping
Expand JoinsJoins
Expand TriggerTrigger
Expand CursorCursor
Expand OperatorsOperators
Expand ConstraintsConstraints
Expand FunctionsFunctions
Expand Conditional ProcessingConditional Processing
Expand LoopingLooping
Expand Error HandlingError Handling
Collapse v.IMP Queriesv.IMP Queries
Expand XMLXML
Expand Query PerformanceQuery Performance
Expand QueriesQueries
Expand NormalizationNormalization
Expand CreateCreate
     

Method 2 SubQry

  
      

      
EMP

      

      ID   
      Country   
      Capital 
      

      

      1
      France
      Paris
      

      

      2
      USA
      Washington
      

      

      3
      France
      Paris
      

      

      4
      India
      New Delhi
      

      

      5
      USA
      Washington
      

      

    
 ---- We can only use this method, if we have ID Column and Duplicate Rows occurs only 2 times  

      
 ---- Find Duplicate Rows  

      SELECT	MAX(ID) as ID, Country, Capital FROM EMP
      GROUP BY Country, Capital
      HAVING COUNT(*) > 1
      

      
Output

      

      ID 
      Country   
      Capital 
      

      

      3
      France
      Paris
      

      

      5
      USA
      Washington
      

      

      
 ---- Delete Duplicate Rows  

      DELETE FROM EMP
      WHERE ID IN(
      SELECT	MAX(ID) FROM EMP
      GROUP BY Country, Capital
      HAVING COUNT(*) > 1)
    
      

      
Output

      

      Country   
      Capital 
      

      

      France
      Paris
      

      

      India
      New Delhi
      

      

      USA
      Washington