SQL add same contents

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?


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

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

DbFiddle demo

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


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;