Mysql Remove Duplicate Data or Rows With DISTINCT

How do I remove duplicate data or rows from a MySQL query?

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.

πŸ₯Ί Was this helpful? Please add a comment to show your appreciation or feedback.

nixCrat Tux Pixel Penguin
Hi! 🀠
I'm Vivek Gite, and I write about Linux, macOS, Unix, IT, programming, infosec, and open source. Subscribe to my RSS feed or email newsletter for updates.

87 comments… add one
  • bede Oct 16, 2012 @ 10:13

    thank you so much jafar! absolutely work even this year is 2012

  • parthiban Dec 24, 2012 @ 8:07

    mysql_query(“delete from mst_result using mst_result,mst_result as vtable where(mst_result.login > vtable.login) and(mst_result.login=vtable.login)”)

    its not working for me friends…

    i want to delete duplicate rows from..
    plz help me…

  • vamsi Jan 31, 2013 @ 16:10

    I want to ask something. I don’t understand how to delete a single row from the below table
    Here are the example
    | user| filename | status |
    vk | Base paper.pdf | Failed |
    | vk | attendance.txt | Faile d |
    vamsi | ppt.pptx | Failed |
    | vamsi2dare621 | ppt.pptx | Failed |
    vamsi2dare621 | Clavister-security-in-the-cloud.pdf | Failed |
    vamsi | ms iv unit material mgt full.pptx | Failed |
    | vamsi2dare621 | ms iv unit material mgt full.pptx | Failed |

    IN the Above table i need to delete the user VK having filename attendence.txt but how to delete the row with out affecting the another user vk with file name Basepaper.pdf
    please give me a quick reply

  • Shahneel Jan 13, 2014 @ 14:05

    Thanks a lot. I have been searching for this…

  • subrahmanyam Aug 2, 2014 @ 18:33

    Simple Solution to Remove duplicates.
    I tried this simple query to remove duplicate title’s from my table – nap_news.

    DELETE n1 from nap_news n1, nap_news n2 WHERE n1.title= n2.title AND n1.id < n2.id

    Hope it will helps someone.

  • sreenath Apr 28, 2015 @ 9:24

    I have so many records in the tabel i have to print details based on the from date and to date.if from date is 01-01-14 and to date is 01-12-14. In this range i have only two records for the id:100. i.e
    no name sal month
    100 sree 10000 01-01-14
    100 sree 20000 01-12-14

    but i need out put like

    no name sal month
    100 sree 10000 01-01-14
    100 sree 0 01-02-14
    100 sree 0 01-03-14
    .
    .
    .
    .
    100 sree 20000 01-12-14

  • Mangaldeep Aug 1, 2015 @ 9:57

    I want to know how to remove duplicate entry form database using DISTINCT Keyword

  • Cuthbert John Sep 25, 2015 @ 8:34

    Thnx for the Answer it simplify my project

  • Reese Jan 4, 2016 @ 14:39

    Thanks!!!

  • enom Aug 4, 2016 @ 8:17

    Great

Leave a Reply

Your email address will not be published. Required fields are marked *

Use HTML <pre>...</pre> for code samples. Your comment will appear only after approval by the site admin.