Lesson 41. Partitioning A Dataset

Sometimes it becomes necessary to discover duplicates in data and then rank order their appearance in the dataset. The specific use case is when you have duplicates in data, but you can delete duplicate records arbitrarily. In other words, it does not matter which record goes, as long as only one record stays.

You can use SELECT OVER to partition your dataset.

Examples

A Basic Partition Example

USE AdventureWorks2016

SELECT ROW_NUMBER() OVER (PARTITION BY CarrierTrackingNumber ORDER BY CarrierTrackingNumber) AS RowNumber, CarrierTrackingNumber
FROM Sales.SalesOrderDetail
WHERE CarrierTrackingNumber IS NOT NULL