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