:::: MENU ::::

SQL Server 2005 ranking function RANK()

  • Nov 11 / 2008
  • 0
dbDigger, Ranking Functions, T-SQL Enhancements

SQL Server 2005 ranking function RANK()

SQL Server 2005 ranking function RANK() returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question. Return data type is bigint. If two or more rows tie for a rank, each tied rows receives the same rank. Syntax of RANK () function is

RANK ( ) OVER ( [ <> ] <> )

<> Divides the result set produced by the FROM clause into partitions to which the RANK function is applied. <> determines the order in which the RANK values are applied to the rows in a partition.

The following example of RANK() from BOL ranks the products in inventory according to their quantities. The rowset is partitioned by LocationID and sorted by Quantity. Notice that the ORDER BY in the OVER clause orders the RANK and the ORDER BY of the SELECT statement orders the result set.

USE AdventureWorks;
GO

SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
,RANK() OVER
(PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS ‘RANK’
FROM Production.ProductInventory i
INNER JOIN Production.Product p
ON i.ProductID = p.ProductID
ORDER BY p.Name
GO
Consult us to explore the Databases. Contact us