How to remove the duplicate rows by using single column in SQL Server

Question

I want to remove duplicate rows in my table. There is a unique column dataframeid. According to the dataframeid, I want to remove duplicate records

Same records shows up 5 times.

Table - OLTMS_5B0395

Sample data

id  DataFrameId DId OT  WT  AT  RC  YC  BC  RV  YV  BV  Oa  Aa  Gt  G   M   P   S   Ot  O   FCNT    RSSI    SF  SNR Rec
2391    1525345797494   4   0   0   35  338 333 664 245 244 245 0   0   0   0   0   0   0   0   0   1243    -92 12  -18 2018-03-05 16:39:00
2459    1525345797494   4   0   0   35  338 333 664 245 244 245 0   0   0   0   0   0   0   0   0   1243    -92 12  -18 2018-03-05 16:39:00
2282    1525345797494   4   0   0   35  338 333 664 245 244 245 0   0   0   0   0   0   0   0   0   1243    -92 12  -18 2018-03-05 16:39:00
2300    1525345797494   4   0   0   35  338 333 664 245 244 245 0   0   0   0   0   0   0   0   0   1243    -92 12  -18 2018-03-05 16:39:00
2338    1525345797494   4   0   0   35  338 333 664 245 244 245 0   0   0   0   0   0   0   0   0   1243    -92 12  -18 2018-03-05 16:39:00

Expected output

 2282   1525345797494   4   0   0   35  338 333 664 245 244 245 0   0   0   0   0   0   0   0   0   1243    -92 12  -18 2018-03-05 16:39:00

id that can be anything but there shouldn't be any duplicate records.

Which columns do you want to remove? Or do you mean "duplicate rows"? Also, what do you mean by "remove"? Delete them from the table, or just exclude them in a query? — Tab Alleman
– Tab Alleman, Commented May 4, 2018 at 18:50
Everything except the id column has the same value. What would be the output from your sample data? — Sean Lange
– Sean Lange, Commented May 4, 2018 at 18:53
Define duplicate? There are zero duplicate rows in your sample data. — Sean Lange
– Sean Lange, Commented May 4, 2018 at 18:54
Possible duplicate of How to keep only one row of a table, removing duplicate rows? — Tab Alleman
– Tab Alleman, Commented May 4, 2018 at 18:56

Yogesh Sharma · Accepted Answer · 2018-05-04 19:03:56Z

0

As per o/p all columns have duplicate values so, you can use row_number() function

delete t from (
     select *,
            row_number() over (partition by DataFrameId,. . . order by id) Seq 
     from OLTMS_5B0395
) t
where Seq > 1;

Be aware with order by clause in row_number() function, include column in partition by clause where you got the duplicate values

edited May 4, 2018 at 19:03

answered May 4, 2018 at 18:57

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

krishna mohan Over a year ago

not all columns are duplicates there is id column which is not duplicate.

krishna mohan Over a year ago

i tried the above query , still have duplicate rows

Tab Alleman Over a year ago

This solution will treat every row with a duplicate DataFrameID as a duplicate, even if all the other columns are different.

Daniel Marcus Over a year ago

Please have a look at the following blog post - this should help thesqlserverdeveloper.blogspot.com/2018/03/…

Collectives™ on Stack Overflow

How to remove the duplicate rows by using single column in SQL Server

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related