从表中的<删除记录.字段的最大数量并保持最大数量 [英] Delete records from a table where < max number for a field and keep highest number

问题描述

我知道这听起来很混乱，但是我不知如何更好地解释它.我有一张简化的桌子:

I know this sounds rather confusing but I'm at a loss how to explain it better. I have a table simplified below:

DB Type       ID
================
Table1         1                 
Table1         2                 
Table1         3                 
Table1         4                 
Table1         5                 
Table2         6                 
Table2         7                 
Table2         8                 
Table2         9                 
Table2        10 

我要实现的目的是基本上清除此表，但如果有意义的话，请保留每个数据库类型具有最高ID的记录-因此，在这种情况下，它将是(Table1,5)和(Table2,10 )，并删除所有其他记录.是否可以通过MySQL专门做到这一点?

what i am trying to achieve is to basically clean out this table but keep the record with the highest ID for each DB Type if that makes sense - so in this case it would be (Table1,5) and (Table2,10) with all other records being deleted. Is it possible to do this exclusively through MySQL?

* EDIT ***

*EDIT***

感谢Yogendra Singh的提示

Answer thanks to tips from Yogendra Singh

DELETE FROM MyTable WHERE ID NOT IN (SELECT * FROM (SELECT MAX(ID) from MyTable GROUP BY DB Type) AS tb1 ) ORDER BY ID ASC

推荐答案

首先尝试按db_type选择最大的ID组，然后将其用作not in的子查询.

TRY selecting the max ID group by db_type first and then use it as sub query with not in.

 DELETE FROM MyTable 
 WHERE ID NOT IN 
    (SELECT ID FROM 
      (SELECT MAX(ID) AS ID from MyTable GROUP BY DB Type) AS tb1
    )

 DELETE FROM MyTable
 HAVING MAX(ID) > ID;

这篇关于从表中的<删除记录.字段的最大数量并保持最大数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！