Thursday, April 26, 2012

Using the T-SQL RANK() function to dynamically number detail rows in master-detail view

The RANK() function will generate a sequence of numbers for detail rows, partitioned by each master row.

The following example was written with SQL 2008 R2 AdventureWorks database.

SELECT a.Name AS SubCategoryName, b.Name As ProductName
, RANK() OVER (PARTITION BY b.ProductSubcategoryID ORDER BY b.ProductID) AS ranknbr
FROM Production.ProductSubcategory AS a
INNER JOIN Production.Product AS b
    ON a.ProductSubcategoryID = b.ProductSubcategoryID

The result set is like this, where the last number is the row number for the detail rows, renumbered each time another master record starts.


Mountain Bikes    Mountain-100 Silver, 38    1
Mountain Bikes    Mountain-100 Silver, 42    2
Mountain Bikes    Mountain-100 Silver, 44    3
Mountain Bikes    Mountain-100 Silver, 48    4
Mountain Bikes    Mountain-100 Black, 38    5
...
Road Bikes    Road-150 Red, 62    1
Road Bikes    Road-150 Red, 44    2
Road Bikes    Road-150 Red, 48    3
Road Bikes    Road-150 Red, 52    4
Road Bikes    Road-150 Red, 56    5
Road Bikes    Road-450 Red, 58    6
...
Touring Bikes    Touring-2000 Blue, 60    1
Touring Bikes    Touring-1000 Yellow, 46    2
Touring Bikes    Touring-1000 Yellow, 50    3
Touring Bikes    Touring-1000 Yellow, 54    4