CODEWITHSUNDEEP
sqlsql-server
Valter Cheque
Valter Cheque

Reputation: 29

DELETE BY MIN VALUE

I have this table in sql:

ID Category Date
1 A 202101
2 A 202101
3 B 202001
4 B 202001

I want to delete the rows with Min(ID)

The expected output is:

ID Category Date
2 A 202101
4 B 202001

A

Upvotes: 0

Views: 938

Answers (2)

Pradeep Kumar
Pradeep Kumar

Reputation: 6979

This one removes all duplicates, irrespective of duplicate count, leaving out only the one with highest ID.

;with cte as
(
    select *, rn = row_number() over (partition by Category order by ID desc) from YourTable
)
delete from cte where rn <> 1

enter image description here

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You can use `DELETE``:

delete from t
    where t.id = (select min(t2.id)
                  from t t2
                  where t2.category = t.category
                 );

You can use similar logic in a SELECT if you just want results in a query:

select t.*
from t
where t.id > (select min(t2.id)
              from t t2
              where t2.category = t.category
            );

Note: Normally, the goal of such an operation is to keep the most recent id rather than deleting the oldest. If that is the case, you can use a variation of the first query:

delete from t
    where t.id < (select max(t2.id)
                  from t t2
                  where t2.category = t.category
                 );

Upvotes: 2

Related Questions