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, May 04 '18 at 18:50
Everything except the id column has the same value. What would be the output from your sample data? — Sean Lange, May 04 '18 at 18:53
Define duplicate? There are zero duplicate rows in your sample data. — Sean Lange, May 04 '18 at 18:54
Possible duplicate of [How to keep only one row of a table, removing duplicate rows?](https://stackoverflow.com/questions/1286843/how-to-keep-only-one-row-of-a-table-removing-duplicate-rows) — Tab Alleman, May 04 '18 at 18:56
id is just serial number that we are not inserting . except serial no. all are duplicate rows — krishna mohan, May 04 '18 at 18:58

Yogesh Sharma · Answer 1 · 2018-05-04T19:03:56.187

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 04 '18 at 19:03

answered May 04 '18 at 18:57

Yogesh Sharma

49,870
5
26
52

not all columns are duplicates there is id column which is not duplicate. – krishna mohan May 04 '18 at 18:59
i tried the above query , still have duplicate rows – krishna mohan May 04 '18 at 19:01
1

This solution will treat every row with a duplicate DataFrameID as a duplicate, even if all the other columns are different. – Tab Alleman May 04 '18 at 19:01
Please have a look at the following blog post - this should help https://thesqlserverdeveloper.blogspot.com/2018/03/deleting-duplicate-records-from-table.html?view=magazine – Daniel Marcus May 04 '18 at 19:02

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

1 Answers1