• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Delete with group by multiple columns exclude and donot exclude - MySQL

 
Ranch Hand
Posts: 94
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

Let's assume i have employee table which has the following 4 columns

id (primary key) - auto generated
name
father_name
mother_name

Assume the following table as input

idnamefather_namemother_name
1IsaacAbrahamSarah
2JacobIsaacRebecca
3JosephJacobRachel
4BenjaminJacobRachel



Assume many duplicate records exist with same name, father name and mother name

Case1: I want to remove all the records except the first inserted record group by name, father name, mother name
Case2: I want to remove all the records except the last inserted record group by name, father name, mother name
Case3: I want to remove all the duplicate records

I'm using mysql. Please advise how to write a delete query for the above 3 cases

Is the following is correct ???

Case1: Keep Latest i.e Highest Id



Case2: Keep Oldest i.e Lowest id



Case3:



Thanks.
 
Saloon Keeper
Posts: 15510
363
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
So given your input table, what output do you expect for the three different cases?
Staff note (Paul Clapham) :

Note: Original post was edited to do that.

 
Joseph Michael
Ranch Hand
Posts: 94
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Output:

Case1: Keep Latest i.e Highest Id

Remove all of the duplicates and removing duplicate keep the highest. Ouput is 1, 2, 4 id will be retained and 3 duplicate will be deleted.

Case2: Keep Oldest i.e Lowest id

Remove all of the duplicates and removing duplicate keep the lowest. Ouput is 1, 2, 3 id will be retained and 4 will be deleted.

Case3: Delete all the duplocates. So output should be 1, 2
 
Stephan van Hulst
Saloon Keeper
Posts: 15510
363
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
But in your example table, 3 and 4 aren't duplicates, they are different people with the same parent names.
 
Joseph Michael
Ranch Hand
Posts: 94
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Updated the table, Stephan.
 
Sheriff
Posts: 7125
184
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Please don't edit old posts.  It makes the thread very hard to understand when you read it for the first time. Instead, post a reply with your changes.
 
Joseph Michael
Ranch Hand
Posts: 94
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Kunte, sure will do that in the future. Please let me know your comments on the latest update.
 
Knute Snortum
Sheriff
Posts: 7125
184
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
See Stephan's post here:

https://coderanch.com/t/730449/databases/Delete-group-multiple-columns-exclude#3398821
 
reply
    Bookmark Topic Watch Topic
  • New Topic