0

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.

ro_quant
  • 11
  • 3
  • If you can only have `1` row per `XRP/USD_FTXSPOT`, wouldn't it be a better idea to `UPDATE` the existing row, rather than `INSERT` a new one and `DELETE` the old one? Then you can add a `UNIQUE CONSTRAINT`/`INDEX` on your `ID` column. – Thom A Jan 05 '22 at 10:11
  • @Larnu probably would be the best solution however I am trying to just pull the data from the DB I can't write to it – ro_quant Jan 05 '22 at 10:18
  • 1
    You don't want to use the term `DELETE` then because people will naturally assume you are trying to delete rows. You just want to not include them in the `SELECT` result? – Martin Smith Jan 05 '22 at 10:22
  • The goal is to reduce the size of the table, so yeah I would need to use DELETE. I want to manipulate the table to only keep the highest level for each – ro_quant Jan 05 '22 at 10:25

1 Answers1

0

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

LukStorms
  • 28,916
  • 5
  • 31
  • 45