Magic Windows Functions: Row Number

Row_Number Function with PARTITION BY

ROW_NUMBER() returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

ROW_NUMBER ( )     OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

Below is an example about usage:

SELECT Emp_Name, Company, Join_Date, Resigned_Date,
ROW_NUMBER() OVER(PARTITION BY Emp_Name, Company ORDER BY Join_Date ) AS Row
FROM Emp_Details
ORDER BY Emp_Name;

Another good example is to help delete duplicate records in a table:

 WITH CTE (Emp_Name, Company, DuplicateEmp)
AS
(
SELECT Emp_Name, Company,
ROW_NUMBER() OVER(PARTITION BY Emp_Name, Company ORDER BY Join_Date) AS DuplicateEmp
FROM Emp_Details
)
DELETE
FROM CTE
WHERE DuplicateEmp> 1
GO