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:
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.
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
...
...
...
...
Note
Notice that several rows have the same rank value and some values are skipped. This is because we are using RANK only. Depending on the requirement, you may want to avoid ties at the same rank value. You can control the rank value with other functions, DENSE_RANK, NTILE, and ROW_NUMBER, as needed. For details on these functions, see the Transact-SQL reference documentation.
Rowset functions
Rowset functions return a virtual table that can be used in the FROM clause as a data source. These functions take parameters specific to the rowset function itself. They include OPENDATASOURCE, OPENQUERY, OPENROWSET, OPENXML, and OPENJSON.
The OPENDATASOURCE, OPENQUERY, and OPENROWSET functions enable you to pass a query to a remote database server. The remote server will then return a set of result rows. For example, the following query uses OPENROWSET to get the results of a query from a SQL Server instance named SalesDB.
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=SalesDB;Trusted_Connection=yes;',
'SELECT Name, ListPrice
FROM AdventureWorks.Production.Product') AS a;
To use remote servers, you must enable some advanced options in the SQL Server instance where you're running the query.
The OPENXML and OPENJSON functions enable you to query structured data in XML or JSON format and extract values into a tabular rowset.
A detailed exploration of rowset functions is beyond the scope of this module. For more information, see the Transact-SQL reference documentation.