SQL add same contents-CodePudding

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

ID           TIME            AMMOUNT

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            AMMOUNT

1              x                5
2              y                3
3              z                3

How can I do this?

CodePudding user response：

You can get the max ammount 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.

CodePudding user response：

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

WITH syntax requires MySQL 8.0.

CodePudding user response：

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;

CodePudding user response：

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.ammount > t.ammount