delete duplicate rows SQL Server

Question

i have column and rows in my table as below

col1  col2  col3  col4
-----------------------
A     1     100   AA
B     2     200   BB
B     1     100   AA
A     2     200   BB

col2, col3 and col4 have duplicate rows, but not in col1. i want the final result is

col1  col2  col3  col4
-----------------------
A     1     100   AA
B     2     200   BB

i've tried this queries, but not Working

DELETE aliasName FROM 
(
    SELECT *, ROW_NUMBER() OVER (
      PARTITION BY col1,col2,col3,col4 ORDER BY col1
    ) AS rowNumber FROM Table) aliasName
WHERE rowNumber > 1

if i remove the PARTITION BY col1, the result is

col1  col2  col3  col4
-----------------------
A     1     100   AA
A     2     200   BB

this my real table result is

JSP/14/06/2017/1810759183   SRK/14/06/2017/1265996087   JS_43345.43556  BB  20000   1
JSP/14/06/2017/800205939    SRK/14/06/2017/2089009939   JS_43345.43556  BB  20000   1

How do you know which value in `col1` to keep? In your sample data, why are you retaining the first and second rows instead of the third and fourth rows? — iamdave, Jun 14 '17 at 10:28
that data is made by looping in my program, there are 3 times looping before it send to database — FannyKaunang, Jun 14 '17 at 10:35
That doesn't answer my question. All 4 rows of data in your question are unique, which means you are actively excluding the 3rd and 4th rows without explaining why. What criteria is used to pick the first 2 rows to keep instead of the second 2 rows? — iamdave, Jun 14 '17 at 10:38
yeah sir, i have i more column is unique that not i including here. total column is 5th..i just need rows of the col1 to keep col2,col3 and col4 together, but not have duplicates in the col1. i hope you understand what i am saying sir — FannyKaunang, Jun 14 '17 at 10:45
What you are asking for makes no sense. **All 4 rows in your question are unique** regardless of the 5th column you have just mentioned. — iamdave, Jun 14 '17 at 10:49
can you help me sir? just skip the criteria that makes you confused, i just need that col1 have no duplicate rows sir — FannyKaunang, Jun 14 '17 at 11:05
try this https://stackoverflow.com/questions/985384/delete-duplicate-records-from-a-sql-table-without-a-primary-key — asmgx, Jun 15 '17 at 01:58

score 0 · Accepted Answer · answered Jun 14 '17 at 10:47

0

try this...

DELETE aliasName FROM 
(
    SELECT *, ROW_NUMBER() OVER (
       ORDER BY col1
    ) AS rowNumber FROM inp) aliasName
WHERE rowNumber%2 = 0

answered Jun 14 '17 at 10:47

Nithin

1,376
12
29

1

thanks for the queries, btw still not working.. col1 have no duplicates with that query, but col2,col3 and col4 have duplicate – FannyKaunang Jun 14 '17 at 10:54
1

DELETE aliasName FROM ( SELECT *, ROW_NUMBER() OVER (partition by col1 order BY col1 ) AS rowNumber FROM inp) aliasName WHERE rowNumber > 1 – Nithin Jun 14 '17 at 10:54
1

what's ur result now ? – Nithin Jun 14 '17 at 11:00
1

SELECT *, ROW_NUMBER() OVER (partition by col1 order BY col1 ) AS rowNumber FROM TABLE can u show me the output of this query – Nithin Jun 14 '17 at 11:05
1

wait sir, on going – FannyKaunang Jun 14 '17 at 11:06
1

col1 col2 col3 col4 ----------------------- A 1 100 AA B 1 100 AA – FannyKaunang Jun 14 '17 at 11:07
1

SELECT *, ROW_NUMBER() OVER (partition by col1 order BY col1 ) AS rowNumber FROM TABLE what about this one – Nithin Jun 14 '17 at 11:10
where is the delete statement sir? – FannyKaunang Jun 14 '17 at 11:15
1

can u jst execute this query and show mw the output of your rowNumber column – Nithin Jun 14 '17 at 11:16
1

col1 col2 col3 col4 rowNumber -------------------- -------------------- -------------------- -------------------- ------- A 1 100 AA 1 A 2 200 BB 2 B 2 200 BB 1 B 1 100 AA 2 are u getting this output – Nithin Jun 14 '17 at 11:17
where i can send you the picture? i take a screenshot for the result sir – FannyKaunang Jun 14 '17 at 11:24
1

add it in your question – Nithin Jun 14 '17 at 11:25
A 1 100 AA 1 A 2 200 BB 2 B 2 200 BB 1 B 1 100 AA 2 – FannyKaunang Jun 14 '17 at 11:26
1

ya then this query will work for sure DELETE aliasName FROM ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY col1 ORDER BY col1 ) AS rowNumber FROM inp) aliasName WHERE rowNumber > 1 – Nithin Jun 14 '17 at 11:28
col1 col2 col3 col4 -------------------- -------------------- -------------------- -------------------- A 1 100 AA B 2 200 BB this is the output u expecting right ? – Nithin Jun 14 '17 at 11:29
oh my God! you're save my life sir!! thank you very very much for helping me a lot.. it's working know! – FannyKaunang Jun 14 '17 at 11:33
i am newly join on here, i dont know to mark it as useful sir.. i just wanna say thank you – FannyKaunang Jun 14 '17 at 11:47
pls click the up arrow on the left side of my answer – Nithin Jun 14 '17 at 11:49
@fannykaunang There is a checkmark next to the answer (the large ✔). Click it to accept the answer. See more about this here: [What should I do when someone answers my question?](https://stackoverflow.com/help/someone-answers) – TT. Jun 14 '17 at 11:49
@Nithin, oopss!! that still not working on my real table.. idk why it just working on my temporary table i've made it – FannyKaunang Jun 14 '17 at 11:56
the result for my real table is still a 1 100 AA b 1 100 AA – FannyKaunang Jun 14 '17 at 12:00

delete duplicate rows SQL Server

1 Answers1