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.ProductSubcategoryIDThe 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
1 comment:
lancel, celine handbags, jimmy choo shoes, bottega veneta, nike roshe, asics running shoes, gucci, giuseppe zanotti, nike huarache, mcm handbags, herve leger, hollister, hollister, new balance, ray ban, p90x workout, soccer shoes, babyliss, longchamp, mac cosmetics, nike trainers, chi flat iron, mont blanc, vans, vans shoes, ghd, iphone cases, nike air max, hollister, ferragamo shoes, ralph lauren, louboutin, nike air max, beats by dre, valentino shoes, converse outlet, lululemon, north face outlet, instyler, soccer jerseys, birkin bag, insanity workout, baseball bats, north face outlet, abercrombie and fitch, timberland boots, reebok shoes, nfl jerseys, oakley, wedding dresses
Post a Comment