This query will work in most variants of SQL (not MySQL < 8.0 or SQL Server); it uses ROW_NUMBER()
to rank the Level
values by ClientID
and removes all rows other than the row with the maximum Level
for that ClientID
value:
WITH CTE AS (
SELECT ClientID, Level,
ROW_NUMBER() OVER (PARTITION BY ClientID ORDER BY Level DESC) AS rn
FROM data
)
DELETE FROM data
WHERE (ClientId, Level) IN (
SELECT ClientId, Level
FROM CTE
WHERE rn > 1
)
SQLite Demo on dbfiddle
In SQL Server you can simply delete from the CTE:
WITH CTE AS (
SELECT ClientID, Level,
ROW_NUMBER() OVER (PARTITION BY ClientID ORDER BY Level DESC) AS rn
FROM data
)
DELETE FROM CTE
WHERE rn > 1
Demo on dbfiddle
In MySQL < 8.0, you can use this query:
DELETE FROM data
WHERE (ClientID, Level) NOT IN (
SELECT ClientID, MAX(Level)
FROM (SELECT * FROM data) d
GROUP BY ClientID
)
Demo on dbfiddle
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…