Tags

,


I ask this question many times in Interview questions and every time I get interesting answers. Recently I got an interesting reply and I thought to post it.

Suppose we have a table which does not any primary key and we have duplicate records in that table and now we want to DELETE the duplicate records.

–CREATING TABLE TO STORE DUPLICATE RECORDS

CREATE TABLE my_test
(
ID INT,
FNAME VARCHAR(10),
MNAME VARCHAR(10)
)
— INSERTING DUPLICATE VALUES IN THE TABLE

INSERT INTO my_test VALUES(1, ‘SAM’,‘ROY’)
INSERT INTO my_test VALUES(2, ‘JAMES’,‘PHILIPS’)
INSERT INTO my_test VALUES(1, ‘SAM’,‘ROY’)
INSERT INTO my_test VALUES(2, ‘JAMES’,‘PHILIPS’)
INSERT INTO my_test VALUES(1, ‘SAM’,‘ROY’)
INSERT INTO my_test VALUES(2, ‘JAMES’,‘PHILIPS’)
INSERT INTO my_test VALUES(3, ‘TRISH’,‘BASS’)

— SELECTING THE VALUES FROM TABLE

— USING CTE – INSERTING THE DISTINCT RECORDS IN ANOTHER TABLE

WITH CTE as(
SELECT ROW_NUMBER() OVER(PARTITION BY ID, FName, MName ORDER BY ID) AS RowID,
*
FROM my_test
)

SELECT ID, FName, MName
INTO my_test_remove_duplicate
FROM CTE
WHERE RowID = 1

— SELECTING FROM THE NEW TABLE

SELECT * FROM my_test_remove_duplicate

I hope you will find this interesting and can use it in real scenarios.

Keep coding and keep Learning!!

Thanks

Subhro Saha

Advertisements