Use ranking and rowset functions

Completed

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.