How can I create a new table by deleted duplicated rows in SQL?

Question

I have a table which looks like this

How can I remove the duplicated rows in SQL so that I am left with a table that looks like this:

ID A B C 
1  1 0 0
2  1 1 0

Try this https://stackoverflow.com/a/18949/5059690 – jobinrjohnson Apr 09 '19 at 08:56 — jobinrjohnson, Apr 09 '19 at 08:56

score 1 · Answer 1 · answered Apr 09 '19 at 08:57

1

use row_number()

with cte as
(
select *, row_number() over(partition by id order by id) as rn
from tablename
)

delete from cte where rn<>1

answered Apr 09 '19 at 08:57

Fahmi

I received an errpr stating row number must have an OVER clause with ORDER BY. how can I alter the code to overcome this error – Beum Apr 09 '19 at 09:31
@Beum, it's already have that, what is your sql server version – Fahmi Apr 09 '19 at 11:22

score 1 · Answer 2 · answered Apr 09 '19 at 09:16

1

select distinct * from <table_name>

This is the simplest way.

answered Apr 09 '19 at 09:16

BlackSwan

2 Answers2