About Linux FAQ

Browse More FAQs:

Mysql remove duplicate data or rows with DISTINCT

Posted by Vivek Gite [Last updated: December 27, 2006]

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:

Discussion on This FAQ

  1. jafar Says:


    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.

  2. jafar Says:

    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!

  3. Glen Rubin Says:

    I have fixed this problem and have a tutorial on my website: http://www.weareallhumansnow.com

  4. Amit Vyas Says:

    select count(*) as num, name,id from table group by name having count(*)>1;

  5. mikemon Says:

    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.

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!

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Click to hear an audio file of the anti-spam word

Copyright © 2006-2008 nixCraft. All rights reserved - TOS/Disclaimer - Privacy policy - Sitemap - Powered by Open source software.