ROW_NUMBER() Example

Using AdventureWorks, demonstrates how ROW_NUMBER can be used to count within groups, in this case counting the number of people in each contact type.

Use AdventureWorks
go
select
 ct.Name
,row_number() over (partition by vc.ContactTypeID order by vc.ContactTypeID)
,LastName + ' , ' + FirstName as Person
from Purchasing.VendorContact vc
inner join Person.ContactType ct
on ct.ContactTypeID = vc.ContactTypeID
inner join Person.Contact c
on c.ContactID = vc.ContactID