Hello team,
I need to remove duplicated rows from a data set.
This is what I found when I searched it in Google:
delete from names a where rowid > (select min(rowid) from names b where b.name=a.name);
Why does it say min in this statement?
select min(rowid) from names b where b.name=a.name
Please advise me.
Thanks,
BittenApple
Removing duplicated rows?
-
- 4StarLounger
- Posts: 550
- Joined: 30 Jul 2014, 23:58
Re: Removing duplicated rows?
Why not use the duplicates query?
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Removing duplicated rows?
burrina,
Thanks,
I ran duplicate query and it worked but how should I delete the duplicated records and keep only one?
I wrote this query:
SELECT First(Table1.Field1) AS [Field1 Field], First(Table1.Field2) AS [Field2 Field], First(Table1.Field3) AS [Field3 Field], First(Table1.Field4) AS [Field4 Field], Count(Table1.Field1) AS NumberOfDups
FROM Table1
GROUP BY Table1.Field1, Table1.Field2, Table1.Field3, Table1.Field4
HAVING (((Count(Table1.Field1))>1) AND ((Count(Table1.Field4))>1));
This query shows duplicated record/ record.
How to delete the duplicated records?
Regards,
BittenApple
Thanks,
I ran duplicate query and it worked but how should I delete the duplicated records and keep only one?
I wrote this query:
SELECT First(Table1.Field1) AS [Field1 Field], First(Table1.Field2) AS [Field2 Field], First(Table1.Field3) AS [Field3 Field], First(Table1.Field4) AS [Field4 Field], Count(Table1.Field1) AS NumberOfDups
FROM Table1
GROUP BY Table1.Field1, Table1.Field2, Table1.Field3, Table1.Field4
HAVING (((Count(Table1.Field1))>1) AND ((Count(Table1.Field4))>1));
This query shows duplicated record/ record.
How to delete the duplicated records?
Regards,
BittenApple
Last edited by BittenApple on 25 Feb 2021, 06:14, edited 1 time in total.
-
- 4StarLounger
- Posts: 550
- Joined: 30 Jul 2014, 23:58
Re: Removing duplicated rows?
If you know which record you want to keep, just filter the query. I would NOT delete ANY records if it were me, I would simply archive them, but up to you.
As always BACK UP your Database before you do anything to it.
Preferred way would be to make a temp table, i.e copy of the original table but structure ONLY. Set it to No duplicates for your criteria then run an append query.
HTH
As always BACK UP your Database before you do anything to it.
Preferred way would be to make a temp table, i.e copy of the original table but structure ONLY. Set it to No duplicates for your criteria then run an append query.
HTH
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Removing duplicated rows?
Hello burrina,
I have 1000,000 rows. Filtering them is very time consuming.
Thanks,
BittenApple
I have 1000,000 rows. Filtering them is very time consuming.
Thanks,
BittenApple
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Removing duplicated rows?
burrina,
I was able to delete duplicated records by grouping and having count>1 on only one field and then change select query to delete query, it worked. But I am looking for a simple way.
Regards,
BittenApple
I was able to delete duplicated records by grouping and having count>1 on only one field and then change select query to delete query, it worked. But I am looking for a simple way.
Regards,
BittenApple
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Removing duplicated rows?
You could do the following:
- Copy the table in the navigation pane, then paste it.
- Select the option to paste the structure only.
- You now have an empty copy of the table.
- Create a Totals (GROUP BY) query based on the original table to return unique records.
- Change it to an Append query with the new table as target, and run it.
- The new table now has unique records only.
- Copy the table in the navigation pane, then paste it.
- Select the option to paste the structure only.
- You now have an empty copy of the table.
- Create a Totals (GROUP BY) query based on the original table to return unique records.
- Change it to an Append query with the new table as target, and run it.
- The new table now has unique records only.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Removing duplicated rows?
Thanks,