sql server - Retaining higher value and deleting lower value in SQL

Question

Happy New Year.

I am unsure how to do an SQL where I need to retain the record for clientID with the highest level while deleting the others?

My Table is like below

And would like to have below

Basically, it removes the duplicate clientID row but retain the highest level of that clientID record.

Thank you for any guidance.

Answer 1

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