Delete duplicates with out cte?

Sai Reddy

8y756

My friend asked me the follwing question.

_______________________________________

Employee

Id Name

1 A

2 B

1 A

5 V

2 B

_____________________________________________________

Delete duplicate rows from the table with out using CTE?

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

I have provided you 2 solutions

good luck

Use this code (it's without CTE)

DECLARE @tblXYZ AS TABLE
( [id] Int
, [Name] nVarChar(10)
)
INSERT INTO @tblXYZ
VALUES (1,'A') , (2, 'B') , (5, 'V') , (1,'A') , (2, 'B')
--- For Test
--SELECT MT.* FROM @tblXYZ MT
--- For Test, for the deletion, just fing the duplicate
--SELECT [RowNumber] = ROW_NUMBER() OVER (PARTITION BY [id] ORDER BY [name] ASC )
-- , MT.*
--FROM @tblXYZ MT
DELETE MT
FROM ( SELECT [RowNumber] = ROW_NUMBER() OVER (PARTITION BY [id] ORDER BY [name] ASC )
, MT.*
FROM @tblXYZ MT
) MT
WHERE [RowNumber] > 1
SELECT MT.*
FROM @tblXYZ MT

You may have....

In your senario the answer is no, why?

as an example i have to delete the Id = 1 and name = 'A' , my question which one.

your answer can be YES just use CTE is more easier

Thank you

Shahriar Nikkhah

Here duplicates were deleted with id=1

Is it possible to keep one value with id=1 and rest should be eleiminated?

i mean

1 A

2 B

5 V