How to remove duplicates from table using SQL query

I would create a new table with a unique index over the columns that you want to keep unique. Then do an insert from the old table into the new, ignoring the warnings about duplicated rows. Lastly, I would drop (or rename) the old table and replace it with the new table. In MySQL, this would look like

CREATE TABLE tmp LIKE mytable; ALTER TABLE tmp ADD UNIQUE INDEX myindex (emp_name, emp_address, sex, marital_status); INSERT IGNORE INTO tmp SELECT * FROM mytable; DROP TABLE mytable; RENAME TABLE tmp TO mytable; 

Or something similar (this is totally untested).

This is not a query but a delete statement. It will delete/remove duplicate rows from your table

;with C as ( select row_number() over(partition by DUPLICATE_VAARS_DECISION order by NODE_EQ_NO) as rn from yourtable ) delete C where rn > 1 

If you are only interested in querying the table and get the non duplicates as a result you should use this instead.

;with C as ( select *, row_number() over(partition by DUPLICATE_VAARS_DECISION order by NODE_EQ_NO) as rn from yourtable ) select * from C where rn = 1 

one way

select emp_name, emp_address, sex, max(marital_status) as marital_status from Yourtable group by emp_name, emp_address, sex 

Since I don't know what you want, I used max for the marital status

See also Including an Aggregated Column's Related Values for more examples

If you are okay with trading space for performance and simplicity then the duplicates in emp_name | emp_address | sex combo can be eliminated, by the introduction of a calculated/derived column using CHECKSUM() TSQL method and DISTINCT keyword while querying.

Heres an example of CHECKSUM :

SELECT CHECKSUM(*) FROM HumanResources.Employee WHERE EmployeeID = 2 

Google around and create a dependent column that contains the checksum of the 3 columns. Then you can select distinct rows by looking at this question

The best answer is here:
Use this SQL statement to identify the extra duplicated rows:

 select * from Employee a 
 where %%physloc%% > 
 (select min(%%physloc%%) from Employee b 
 where a.emp_name=b.emp_name and a.emp_address=b.emp_address and a.sex=b.sex); 

you will get the extra row:

uuuu eee m s 

Use this SQL statement to delete the extra duplicated rows:

 delete from Employee a 
 where %%physloc%% > 
 (select min(%%physloc%%) from Employee b 
 where a.emp_name=b.emp_name and a.emp_address=b.emp_address and a.sex=b.sex); 
 

For all duplicated records, only the one with lowest physical location is kept. This method can be applied to remove all kinds of duplicated rows.

I am assuming that you use MS SQL Server. If you are using Oracle DB, then you can just replace '%%physloc%%' with 'rowid'

Enjoy the code!

I know this is old post, but recently I tested a solution and want to share if any one can find my solution helpful -

CREATE TABLE tmpTable LIKE yourTable; insert into tmpTable (col1, col2 ... colN) SELECT distinct col1, col2 ... colN FROM yourTable WHERE 1; drop table yourTable; RENAME TABLE tmpTable TO yourTable;

Please note, insert into statement may execute without primary key.

Thanks.

If you don't satisfied with distinct try below

SELECT MAX(ID) AS MaxRecordID, max(FirstName) AS fname FROM [SampleDB].[dbo].[Employee] GROUP BY [FirstName], [LastName], [Country] 

Use the Max key word with groupBy. You can use max for any type column. Integer, Varchar and etc.