CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012duplicates
FannyKaunang
FannyKaunang

Reputation: 91

SQL Server - Delete Duplicate Rows that based on different rows from another field

i have column and rows in my table as below

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

i want the final result is

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

OR

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

i want to delete first and second rows OR third and fourth rows but based on col1, as you can see, their's not same with each other rows except with the col0, because the col0 is primary key. how should i do with sql server express 2012?

Upvotes: 0

Views: 65

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521467

Here is an option for deleting the first pair of duplicate records. You can assign a row number based on a partition of the four data columns. Do this in a CTE, and then delete all records from that CTE where the row number is 1.

WITH cte AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY col2, col3, col4 ORDER BY col0) rn
    FROM yourTable
)
DELETE
FROM cte
WHERE rn = 1

Follow the link below for a demo showing that the logic of my CTE is correct.

Demo

Upvotes: 1

Tien Nguyen Ngoc
Tien Nguyen Ngoc

Reputation: 1555

You can use this

DELETE FROM yourTable
WHERE col0 NOT IN ( SELECT MIN(col0) FROM yourTable GROUP BY col1)

Upvotes: 0

Related Questions