Remove duplicate lines in sql server

Question

I have a table with the following example format:

ID Name 
1  NULL
1  NULL
2  HELLO 
3  NULL
3  BYE

My goal is to remove repeated lines with same IDS, but with restrictions. According to the example, I need to remove a row with ID-1, and the row with ID-3 and with no value (NULL).

I would stick with the table:

ID Name 
1  NULL
2  HELLO 
3  BYE

How can I do this in sql server? thank you

Have a look here http://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows — Moslem Ben Dhaou, Mar 08 '14 at 18:38
Do you want to just select the result, or actually remove the rows from the existing table? — Joachim Isaksson, Mar 08 '14 at 18:39

Joachim Isaksson · Accepted Answer · 2014-03-08T18:57:55.287

To just select the data, you can use a simple CTE (common table expression);

WITH cte AS (
  SELECT id, name,
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY name DESC) rn
  FROM myTable
)
SELECT id,name FROM cte WHERE rn=1;

An SQLfiddle to test with.

If you mean to delete the duplicates from the table and not just select the data without updating anything, you could use the same CTE;

WITH cte AS (
  SELECT id, name,
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY name DESC) rn
  FROM myTable
)
DELETE FROM cte WHERE rn<>1;

Another SQLfiddle to test with, and remember to always back up your data before running destructive SQL statements from random people on the Internet.

Remove duplicate lines in sql server

1 Answers1