What is Use ranking and rowset functions
Ranking and rowset functions aren’t scalar functions because they don’t return a single value. These functions accept a set of rows as input and return a set of rows as output.
Ranking functions
Ranking functions allow you to perform calculations against a user-defined set of rows. These functions include ranking, offset, aggregate, and distribution functions.
This example uses the RANK function to calculate a ranking based on the ListPrice, with the highest price ranked at 1:
SQLCopy
SELECT TOP 100 ProductID, Name, ListPrice,
RANK() OVER(ORDER BY ListPrice DESC) AS RankByPrice
FROM Production.Product AS p
ORDER BY RankByPrice;
The query results might look like:
ProductID
Name
ListPrice
RankByPrice
749
Road-150 Red, 62
3578.27
1
750
Road-150 Red, 44
3578.27
1
751
Road-150 Red, 48
3578.27
1
771
Mountain-100 Silver, 38
3399.99
4
772
Mountain-100 Silver, 42
3399.99
4
775
Mountain-100 Black, 38
3374.99
6
…
…
…
…
OVER
You can use the OVER clause to define partitions, or groupings within the data. For example, the following query extends the previous example to calculate price-based rankings for products within each category.
SQLCopy
SELECT c.Name AS Category, p.Name AS Product, ListPrice,
RANK() OVER(PARTITION BY c.Name ORDER BY ListPrice DESC) AS RankByPrice
FROM Production.Product AS p
JOIN Production.ProductCategory AS c
ON p.ProductCategoryID = c.ProductcategoryID
ORDER BY Category, RankByPrice;
The results of this query might look something like this:
Category
Product
ListPrice
RankByPrice
Bib-Shorts
Men’s Bib-Shorts, S
89.99
1
Bib-Shorts
Men’s Bib-Shorts, M
89.99
1
Bike Racks
Hitch Rack – 4-Bike
120
1
Bike Stands
All-Purpose Bike Stand
159
1
Bottles and Cages
Mountain Bottle Cage
9.99
1
Bottles and Cages
Road Bottle Cage
8.99
2
Bottles and Cages
Water Bottle – 30 oz.
4.99
3
Bottom Brackets
HL Bottom Bracket
121.49
1
Bottom Brackets
ML Bottom Bracket
101.24
2
Bottom Brackets
LL Bottom Bracket
53.99
3