How to make a query in the database to delete rows, with calculation?

Question

SQL

There is a table in the database which has fields
BLOCK_NUMBER | POST | RATING
1 | ... | 3
1 | ... | 5
1 | ... | 2
2 | ... | 7
2 | ... | 5
And so on.
BLOCK_NUMBER - These are the designations in which block the post is located, in each block there can be from 2 posts, and these blocks can be from 2 or more.
So, I need to make a query that will go through this entire table and delete all posts for each block, leaving only those with the highest rating, i.e. if, for example, make such a query following the example of the table above, then only rows 2 and 4 will be left behind Tell me how to make such a request?

Answer 1

following the example of the table above, then only rows 2 and 4 will remain

Let's start simple: tables don't have row numbers , only sort order. Therefore, you must uniquely identify them (there are many ways, for example, the simplest one is an auto-increment integer ID).

those with the highest rating

Compose a query by grouping by blocks and calculating the maximum rating value. Use it as a sub-query when deleting and the IN operator to not delete records that match it.

Answer 2

DELETE `t`.*
  FROM (
    SELECT `BLOCK_NUMBER`, MAX(`RATTING`) AS `rating`
    FROM `table`
    GROUP BY `BLOCK_NUMBER`
  ) AS `t1` 
  JOIN `table` AS `t` ON `t`.`BLOCK_NUMBER` = `t1`.`BLOCK_NUMBER`
    AND `t`.`RATTING` != `t1`.`rating`