0

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?

Improve this question

2 Answers 2

Reset to default
1

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

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

10 Comments

still not working sir, their delete the same rows. if iam including the col1, it's nothing to delete
@fannykaunang Sorry I didn't look closely enough at your data. Just remove col1 from the partition and it should work.
there are deleting the duplicate on col2.col3 and col4 but col1 still duplicate sir if i remove the col1 from the partititon
i think is the partition is not the solution on this
thanks @tim, i corrected my asumming for the partition on this. know their working good perfect sir. thank you very much!!
|
0

You can use this

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

10 Comments

yeah, that's working perfect! but, what the impact of that queries if the duplicate rows is thousand?
What problem do you have? Will it be slow?
i just feel worried if that queries deleting wrong records haha. anyway thankyou so much for helping @tien
what i am worried is happening, that queries have delete the wrong records. i was tested it with 16 record that have 4 duplicates each others
Can you give me your data? I try test.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.