Mysql remove duplicate data or rows with DISTINCT
Q. How do I remove duplicate data or rows from a MySQL query?
A. If output from a MySQL query contains duplicate data or row and if you would like to remove the same use DISTINCT.
DISTINCT combined with ORDER BY needs a temporary table in many cases.
Consider following query:
mysql> SELECT firstname FROM address;
If you need unique firstname i.e remove duplicate contains type the command:
mysql> SELECT DISTINCT firstname FROM address;
You can use DISTINCT with multiple column as follows:
mysql> SELECT DISTINCT firstname, city FROM address;
For DISTINCT optimization please see this help page.
Subscribe to our free e-mail newsletter or RSS feed to get all updates.
You can Email this page to a friend.
Related Other Helpful FAQs:
- Create a mysql database, tables and insert data
- MySQL command to show list of databases on server
- Howto: Use mysql or run mysql queries from shell script
- Securing MySQL server
- How do I access MySQL server from the shell prompt (command line)?
Discussion on This FAQ
Leave a Reply
We encourage your comments, and suggestions. But please stay on topic, be polite, and avoid spam. Thank you very much for stopping by our site!
January 21st, 2008 at 6:03 pm
delete from table1
USING table1, table1 as vtable
WHERE (NOT table1.ID=vtable.ID)
AND (table1.field_name=vtable.field_name)
1. Here you tell mysql that there is a table1.
2. Then you tell it that you will use table1 and a virtual table with the values of table1.
3. This will let mysql not compare a record with itself!
4. Here you tell it that there shouldn’t be records with the same field_name.
January 21st, 2008 at 6:22 pm
The DISTINCT command is not working somehow!
Also the delete rule what I send early is not good enough:
It deletes all values which are duplicate,
instead of leaving one behind!
February 24th, 2008 at 12:29 am
I have fixed this problem and have a tutorial on my website: http://www.weareallhumansnow.com
March 3rd, 2008 at 10:24 am
select count(*) as num, name,id from table group by name having count(*)>1;
March 25th, 2008 at 6:05 pm
The first answer by jafar is close, you just need some condition that is true for all except one of the duplicates.
I ended up using:
delete from table1
USING table1, table1 as vtable
WHERE (table1.ID > vtable.ID)
AND (table1.field_name=vtable.field_name)
Used “greater than” instead of “not equal”. This handles both the conditions that the records not be the same and that only one record will return false.
Since we use greater than (or less than), only the first (or last) duplicate returns false, so it is not selected for deletion.