Delete Occurrence of Unique ID from SQL Server Table

January 5, 2022

I have a SQL Server Table where I have a Column that contains a unique ID. I also have another column called Level, every time a new occurrence of a unique ID enters the table the Level will increase.

ID               Level  DateTime                Symbol  Exchange
XRP/USD_FTXSPOT  1      2022-01-04 17:03:24.027 XRP/USD FTX
XRP/USD_FTXSPOT  2      2022-01-04 17:03:31.147 XRP/USD FTX

Therefore it would look something like this. The more recent the row entered the higher the level gets basically.

What I am trying to do is be able to say once a new row is entered for a unique ID, remove all previous occurrences based on its Level. Meaning, remove all rows where the level is < the greatest.

SELECT * FROM
Thursday_crypto JOIN
(
SELECT ID, MAX(Level) Level
FROM Thursday_crypto
GROUP BY ID
) max_date ON Thursday_crypto.ID = max_date.ID AND Thursday_crypto.Level = max_date.Level

I have this which basically returns the rows where each unique ID has its highest Level. But I am wondering how I can alter this to then remove all rows not within this selection. I want to reduce the size of the table, so I guess my main goal is to remove all rows not within this selection.

>Solution :

You can calculate a row_number based on the ID and the level.
Then remove the dups based on the row_number.

WITH CTE_DATA AS (
  SELECT [RowNum] = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Level DESC)
  FROM Thursday_crypto
) 
DELETE 
FROM CTE_DATA
WHERE RowNum > 1

Demo on db<>fiddle here