In the attached table, I'm trying to identify and delete the duplicate records which have 0 rates.
E.G. line #2 and line #5. I want to keep the records with rates as 0 that do not have duplicate entries.
I'm using the query below, and it's not giving me the results I need. It's assigning row number=1 for entries where rates are 0, and then row number=2 for the valid record.
I think I may need an additional subquery or something?
WITH CTE AS( SELECT tblname.*,
RN = ROW_NUMBER()OVER(PARTITION BY region, state, month
ORDER BY rate, rate2, rate3 DESC) FROM tablename )
select * FROM CTE WHERE RN = 1
