MySQL Forums
Forum List  »  Newbie

Re: MySQL get highest value and delete other rows
Posted by: Peter Brawley
Date: July 06, 2018 08:45AM

> a MySQL database called parent_reply.

Assuming you mean table parent_reply, this gives the comment_id with the top score for a given parent_id x ...

select comment_id, score,
from parent_reply
where parent_id=x
order by score desc limit 1;

... so the rows to delete are rows with the same parent_id and different comment_ids, which is an exclusion join ...

delete a.*
from parent_reply as a
left join (
  select parent_id, comment_id
  from parent_reply
  order by score desc limit 1
) as b using(parent_id, comment_id)
where a.parent_id=x and b.comment_id is null;

You've not provided your table & data, so test before running in production.

Options: ReplyQuote


Subject
Written By
Posted
Re: MySQL get highest value and delete other rows
July 06, 2018 08:45AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.