SQL: SQL Server ROW_NUMBER Using PARTITION to Group Row Numbers

5/19/2015 3:08:59 PM

I recently ran into a problem where I needed to return data representing financial charges against a recurring subscription. I needed to have 1 query that returned the subscription data with its first charge and a second query that returned the subscription data with all subsequent charges. I accomplished this use the ROW_NUMBER function using a PARTITION. The PARTITION feature essentially lets you use the ROW_NUMBER function over parts of the data. In this case, the ROW_NUMBER function using a parent SubscriptionId as the value to iterate over and each unique SubscriptionId had its own iterator.

SELECT			
			Subscription.Id AS 'Subscription Id'
			,SubscriptionCharges.Id AS 'Charge On Subscription Id'
			,SubscriptionCharges.ChargeDate AS 'Charge On Subscription Date'
			
			,ROW_NUMBER() OVER(PARTITION BY SubscriptionCharges.SubscriptionId ORDER BY SubscriptionCharges.Id ASC) AS ChargeCount

FROM		Subscriptions
			JOIN SubscriptionCharges ON Subscription.Id = SubscriptionCharges.SubscriptionId

ORDER BY	Subscription.id