Sometime we may need to filter duplicate records and remove them from a given table. The one way to do this is to use a windows functions ( row_number() and rank() ) with CTE.
Let us see this in action:
Here we have a table with a duplicated rows. If we zoom in to row 1 and 2 we could see that we have the same first name and last name, when we look at our window functions row_number() give as a squential numbers that increment with 1 but when we look rank() every time there is a duplicate it will have the same value as the preceding value and in the next record set it will have the same value as row_number and the sequence continues this way.