How to remove all duplicate records from SQL table?

Question

Hello, I have the name of the friendsData strong> table , which contains duplicate entries as shown below

fID UserID  FriendsID       IsSpecial      CreatedBy
-----------------------------------------------------------------
1   10         11            FALSE            1
2   11          5            FALSE            1
3   10         11            FALSE            1
4    5         25            FALSE            1 
5   10         11            FALSE            1
6   12         11            FALSE            1
7   11          5            FALSE            1
8   10         11            FALSE            1
9   12         11            FALSE            1

I want to delete rows of duplicate combinations using MS SQL?
Delete the latest duplicate entries from the MS SQL FriendsData table. here I have attached an image that highlights duplicate column combinations.

How can I remove all duplicate combinations from an SQL table?

Answer 1

try it

DELETE
FROM FriendsData 
WHERE fID NOT IN
(
SELECT MIN(fID)
FROM FriendsData 
GROUP BY UserID, FriendsID)

Look here

- ,

,

Answer 2

, ( ). , :

with cte as (
  select *, 
     row_number() over (partition by userid, friendsid order by fid) as [rn]
  from FriendsData
)
delete cte where [rn] <> 1

fid. - , order by over.

, , . , !

Answer 3

, MS-SQL, MySQL :

DELETE FROM FriendsData WHERE fID 
       NOT IN ( SELECT fID FROM FriendsData 
                   GROUP BY UserID, FriendsUserID, IsSpecial, CreatedBy)

GROUP BY , , ,

Answer 4

,

  select * from FriendsData f1, FriendsData f2
  Where f1.fID=f2.fID and f1.UserID  =f2.UserID  and f1.FriendsID  =f2.FriendsID

If it returns you duplicate rows, replace Select * with "Delete"

which will solve your problem