CODEWITHSUNDEEP
mysqlsqlselect
JazziJeff
JazziJeff

Reputation: 731

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

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***

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

Upvotes: 3

Views: 3181

Answers (5)

sebt
sebt

Reputation: 525

Is the combination DB Type - ID unique?

If so, you can attack this in two stages:

  1. Get only the rows you want

    SELECT [DB Type], Max(ID) AS MaxID
FROM YourTable
GROUP BY [DB Type]

  2. Delete the rest (Wrapping the previous statement into a more complicated statement; don't mean that)

    DELETE FROM YourTable
FROM
    YourTable
    LEFT JOIN
    (SELECT [DB Type], Max(ID) AS MaxID
     FROM YourTable GROUP BY [DB Type]) DontDelete
    ON
      YourTable.[DB Type]=DontDelete.[DB Type] AND
      YourTable.ID=DontDelete.MaxID
WHERE
  DontDelete.[DB Type] IS NULL

Upvotes: 1

user2499725
user2499725

Reputation: 1

delete from my_Table  
 where Day in (select  MAX(day) d from my_Table   where id='id')

Upvotes: 0

wildplasser
wildplasser

Reputation: 44250

DELETE FROM MyTable del
WHERE EXISTS ( 
    (SELECT * 
     FROM MyTable xx
     WHERE xx."db Type" = del."db Type"
     AND xx.id > del.id
     );

Upvotes: 0

fthiella
fthiella

Reputation: 49049

delete your_table
from
  your_table left join
  (select max(id) max_id from your_table group by type) mx
  on your_table.id=mx.max_id
where mx.max_id is null

Subquery returns the maximum id for every type, and those are the values to keep. With an left join i'm selecting all the rows from your table that don't have an in in max_ids, and those are the rows to delete. This will work only if id is primary key, otherwise we have to join also the type.

Upvotes: 1

Yogendra Singh
Yogendra Singh

Reputation: 34367

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
    )

EDIT: 

 DELETE FROM MyTable
 HAVING MAX(ID) > ID;

Upvotes: 3

Related Questions