How to remove duplicate rows with the same column in SQL

Question

I have the following table in SQL:

I want to remove rows with the same id value.

Which query in SQL should I use?

The table should be as follows after the delete operation:

Answer 1

You could select all unique ids by using group by:

Select 
   max(a)
   ,id
   from Table
   group by id

If the above result is the result that you want to keep in your table then you could just do that:

delete FROM Table
  where a not in (
    Select max(a)  from Table
    group by id
  )

Answer 2

Use Distinct keyword

SELECT DISTINCT a, id, c FROM table_name;

The DISTINCT keyword ensures that only unique rows are returned in the query result.

or use group by and having clause

SELECT a,id,c, COUNT(*) AS CNT FROM [SampleDB].[dbo].[Employee]
GROUP BY a,id,c HAVING COUNT(*) > 1;

==============================

DELETE FROM [SampleDB].[dbo].[Employee] WHERE a NOT IN(SELECT MAX(a) AS MaxRecordID FROM [SampleDB].[dbo].[Employee] GROUP BY id,c);

explanation is given on below source.If it donot work read the source and change accordingly

Source:https://www.sqlshack.com/different-ways-to-sql-delete-duplicate-rows-from-a-sql-table/

Answer 3

Let's say the table is called t, maybe a way to remove duplicates can be the use the distinct, on that column, so in your case

SELECT DISTINCT id, a, c FROM t;

Bye!

Answer 4

If you use SQL Server, I'd prefer a common table expression with an Over-clause, since it makes the SQL query readable and maintainable:

WITH CTE AS 
(
   SELECT 
       [a], [id], [c], 
       RN = ROW_NUMBER() OVER (PARTITION BY id ORDER BY a)
   FROM dbo.TableName
)
DELETE FROM CTE 
WHERE RN > 1

You could for example easily change it to a select see what you'll delete:

WITH CTE AS
(
   SELECT 
       [a], [id], [c], 
       RN = ROW_NUMBER() OVER (PARTITION BY id ORDER BY a)
   FROM dbo.TableName
)
SELECT * 
FROM CTE 
WHERE RN > 1

You can also easily control what you'll keep, in this case I keep the first duplicate ordered by a.