SQL add same contents

Question

How can I delete all columns that are duplicate and don't have the biggest "amount". I have the following table:

ID           TIME            AMOUNT
-----------------------------------
1              x                5
2              y                1
2              y                3
3              z                1
3              z                2
3              z                3

But I want it to be like this, so that only the column which has the biggest number "survives":

ID           TIME            AMOUNT
------------------------------------
1              x                5
2              y                3
3              z                3

How can I do this?

Answer 1

You can get the max amount per id and time and then get the rows matching:

select t.Id, t.Time, t.amount
from myTable t 
inner join 
    (select Id, time, max(amount) as amt
     from myTable
     group by Id, Time) tmp on t.id = tmp.id and
                            t.time = tmp.time and
                            t.amount = tmp.amt

DbFiddle demo

EDIT: You may want to add DISTINCT depending on your needs.

Answer 2

One other approach using a CTE

with del as (
  select *, 
    First_Value(amount) over(partition by id order by amount desc) maxamount
  from t
)
delete from t
using t join del on t.id = del.id and t.amount < maxamount;

Answer 3

WITH cte AS 
(
    SELECT 
        ID, 
        ROW_NUMBER() OVER (PARTITION BY TIME ORDER BY AMOUNT DESC) AS ROWNUM
    FROM 
        MyTable
)
DELETE MyTable 
FROM MyTable 
JOIN cte USING (ID)
WHERE ROWNUM > 1;

WITH syntax requires MySQL 8.0.

Answer 4

I think some of the answers here are overly complicated.

delete t 
from yourtable t 
join yourtable t2 on t.id = t2.id 
                  and t.time = t2.time 
                  and t2.amount > t.amount