Win a copy of Transfer Learning for Natural Language Processing (MEAP) this week in the Artificial Intelligence and Machine Learning forum!
  • 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • Paul Clapham
  • Devaka Cooray
  • Bear Bibeault
Sheriffs:
  • Junilu Lacar
  • Knute Snortum
  • Liutauras Vilda
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Piet Souris
Bartenders:
  • salvin francis
  • Carey Brown
  • Frits Walraven

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

 
Ranch Hand
Posts: 85
  • Mark post as helpful
  • send pies
  • 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: 11881
253
  • Mark post as helpful
  • send pies
  • 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: 85
  • Mark post as helpful
  • send pies
  • 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: 11881
253
  • Mark post as helpful
  • send pies
  • 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: 85
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Updated the table, Stephan.
 
Sheriff
Posts: 7051
184
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
  • 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: 85
  • Mark post as helpful
  • send pies
  • 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: 7051
184
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
See Stephan's post here:

https://coderanch.com/t/730449/databases/Delete-group-multiple-columns-exclude#3398821
 
The airline is called "Virgin"? Don't you want a plane to go all the way? This tiny ad will go all the way:
Two software engineers solve most of the world's problems in one K&R sized book
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic