question

ksaint avatar image
ksaint asked

Removing duplicate entries query not working

table.jpg

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

sqlcteduplicate values
table.jpg (18.5 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered

Try changing the order to

ORDER BY rate DESC, rate2 DESC, rate3 DESC
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.