sql remove duplicate rows code example

Example 1: t-sql get duplicate rows

SELECT [CaseNumber], COUNT(*) AS Occurrences
FROM [CaseCountry]
GROUP BY [CaseNumber]
HAVING (COUNT(*) > 1)

Example 2: sql server delete records that have a single duplicate column

WITH cte AS (
    SELECT 
        contact_id, 
        first_name, 
        last_name, 
        email, 
        ROW_NUMBER() OVER (
            PARTITION BY 
                first_name, 
                last_name, 
                email
            ORDER BY 
                first_name, 
                last_name, 
                email
        ) row_num
     FROM 
        sales.contacts
)
DELETE FROM cte
WHERE row_num > 1;

Example 3: how to query without duplicate rows in sql

SELECT DISTINCT col1,col2... FROM table_name where Condition;

Example 4: how to remove duplicate in sql

Distinct: helps to remove all the duplicate
records when retrieving the records from a table.

SELECT DISTINCT FIRST_NAME FROM VISITORS;

Example 5: query delete duplicates

DELETE FROM dups a USING (
      SELECT MIN(ctid) as ctid, key
        FROM dups 
        GROUP BY key HAVING COUNT(*) > 1
      ) b
      WHERE a.key = b.key 
      AND a.ctid <> b.ctid