Deleting all duplicate rows but keeping one

When you work with Power Bi or Power Pivot duplicates are not allowed but due to different problems or error I know there are some duplicates in this table.

I need to eliminate the extra rows from the perspective of the key columns. Some other columns may have slightly different data but I do not care about that. I still need to keep one of these rows however.

SELECT DISTINCT won’t work because it operates on all columns and I need to suppress duplicates based on the key columns.

How can I delete the extra rows but still keep one efficiently?

It’s very simple you have only to use this query:

WITH cte AS (
  SELECT[KEY1], [KEY2], 
     row_number() OVER(PARTITION BY KEY2, KEY1 ORDER BY KEY1) AS [rn]
  FROM TABLE
)
DELETE cte WHERE [rn] > 1


With this simple query you can delete al duplicate row keeping one. You can use order by to keeping for example the last raw (order by date asc).

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo di WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione /  Modifica )

Google photo

Stai commentando usando il tuo account Google. Chiudi sessione /  Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione /  Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione /  Modifica )

Connessione a %s...