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.


A Basic Partition Example
In [ ]:
USE AdventureWorks2016
SELECT ROW_NUMBER() OVER (PARTITION BY CarrierTrackingNumber ORDER BY CarrierTrackingNumber) AS RowNumber, CarrierTrackingNumber
FROM Sales.SalesOrderDetail
WHERE CarrierTrackingNumber IS NOT NULL
Copy link