SQL: How to Identify and Remove Duplicate Records from a Table? Part4

Опубликовано: 07 Май 2025
на канале: GoLearningPoint

136

Method 3 : Using Analytical Function ROW_NUMBER()

ROW_NUMBER() is a Analytical function to rank the rows for each group of column(combination of columns).

/*Actual Query - Keep the records having maximum ROWID for combination or records and remove rest of the records*/
Delete From DupTest Where ROWID
IN
(
Select Rid
From
(
Select ROWID Rid, ID, NAME, SALARY,
ROW_NUMBER() Over(Partition By ID Order By ID) Rn
From DupTest
) Where Rn [GREATER THAN OPERATOR] 1
);

/*Check the result now*/
Select * From DupTest;

Method 4 : Using RANK/DENSE RANK () Analytical Functions

/*Using RANK Actual Query - Keep the records having maximum ROWID for combination or records and remove rest of the records*/
Delete From DupTest Where ROWID
IN
(
Select Rid
From
(
Select ROWID Rid, ID, NAME, SALARY,
RANK() Over(Partition By ID Order By ROWID) Rn
From DupTest
) Where Rn [GREATER THAN OPERATOR] 1
);

/*Check the result now*/
Select * From DupTest;

/*Using DENSE_RANK Actual Query - Keep the records having maximum ROWID for combination or records and remove rest of the records*/
Delete From DupTest Where ROWID
IN
(
Select Rid
From
(
Select ROWID Rid, ID, NAME, SALARY,
DENSE_RANK() Over(Partition By ID Order By ROWID) Rn
From DupTest
) Where Rn [GREATER THAN OPERATOR] 1
);

/*Check the result now*/
Select * From DupTest;

#GoLearningPoint

For Interview questions and other topics -
https://tipsfororacle.blogspot.com/