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.
Using CTE we could remove the duplicated values as it is described below.
After executing the previous script when if we check the table again there are no duplicate values.
Read more:
CTE-
http://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx
Window Functions-
http://technet.microsoft.com/en-us/library/ms189461.aspx
http://technet.microsoft.com/en-us/library/ms189798.aspx
http://technet.microsoft.com/en-us/library/ms186734.aspx
