:::: MENU ::::

SQL Server 2005 ranking function ROW_NUMBER()

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

SQL Server 2005 ranking function ROW_NUMBER()

SQL Server 2005 ranking function ROW_NUMBER() returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. Syntax for ROW_NUMBER function is

 
ROW_NUMBER ( ) OVER ( [ ] )  

It divides the result set produced by the FROM clause into partitions to which the ROW_NUMBER() function is applied. Determines the order in which the ROW_NUMBER() value is assigned to the rows in a partition. The following example of ROW_NUMBER() from BOL returns the ROW_NUMBER for the salespeople in AdventureWorks based on the year-to-date sales.

 
USE AdventureWorks;  
 GO  
 SELECT c.FirstName, c.LastName  
 ,ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number'  
 ,s.SalesYTD, a.PostalCode  
 FROM Sales.SalesPerson s  
 INNER JOIN Person.Contact c  
 ON s.SalesPersonID = c.ContactID  
 INNER JOIN Person.Address a  
 ON a.AddressID = c.ContactID  
 WHERE TerritoryID IS NOT NULL  
 AND SalesYTD <> 0  
 GO  

The following example returns rows with numbers 50 to 60 inclusive in the order of the OrderDate.

 
USE AdventureWorks;  
 GO  
 WITH OrderedOrders AS  
 (  
 SELECT SalesOrderID, OrderDate,  
 ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'  
 FROM Sales.SalesOrderHeader  
 )  
 SELECT *  
 FROM OrderedOrders  
 WHERE RowNumber BETWEEN 50 AND 60  

The following example shows using the PARTITION BY argument.

 
USE AdventureWorks;  
 GO  
 SELECT c.FirstName, c.LastName  
 ,ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number'  
 ,s.SalesYTD, a.PostalCode  
 FROM Sales.SalesPerson s  
 INNER JOIN Person.Contact c  
 ON s.SalesPersonID = c.ContactID  
 INNER JOIN Person.Address a  
 ON a.AddressID = c.ContactID  
 WHERE TerritoryID IS NOT NULL  
 AND SalesYTD <> 0  
 GO  
Consult us to explore the Databases. Contact us