Delete duplicates

[damipera]

hi guys, can you give me some advice how to write the SQL to delete the duplicates? thanks for any suggestion.


something like this

id----myWords----chkbox1----chkbox2----chkbox3
1------apple----------x
2------orange----------------------------------x
3------apple----------x
4------orange----------------------------------x
5------apple----------x
6------banana---------------------x
7------orange----------------------------------x
8------banana---------------------x
9------apple----------x


then after deleting the duplicates, to look like this table


id----myWords----chkbox1----chkbox2----chkbox3
1------apple----------x
2------orange----------------------------------x
6------banana---------------------x


note: x, is a ticked checkbox

 

[dhulbert]

SELECT MIN(ID) AS ID, myWords, chkbox1, chkbox2, chkbox3
INTO #TEMP001
FROM YOURTABLE
GROUP BY myWords, chkbox1, chkbox2, chkbox3

TRUNCATE YOURTABLE

INSERT INTO YOURTABLE
SELECT *
FROM #TEMP001


I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[simian336]

delete yourtable
where id not in (select min(id) from yourtable as test group by myWords)

Simi

 

[markros]

;with cte as (select *, row_number() over (partition by myWords order by ID) as Rn)

delete from cte where Rn > 1

PluralSight Learning Library