inforakesha

A fine WordPress.com site

SQL SERVER Example of RANKING Functions – ROW_NUMBER, RANK, DENSE_RANK, NTILE

Leave a comment

ROW_NUMBER () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of each row within the partition of a result set.

DENSE_RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of rows within the partition of a result set, without any gaps in the ranking.

NTILE (integer_expression) OVER ([<partition_by_clause>] <order_by_clause>)
Distributes the rows in an ordered partition into a specified number of groups.

All the above definition and syntax are taken from BOL. It is difficult to explain above function anything else than what they are explained in BOL. Following example is excellent example from BOL again. This function explains usage of all the four function together in one query.
—RANKING FUNCTIONS
–ROW_NUMBER, RANK, DENSE_RANK NTILE
–The ROW_NUMBER function assigns a number from 1 to n based on a user-specified sorting order
–ROW_NUMBER() OVER (ORDER BY <Column_Name>)
— ROW_NUMBER() OVER (PARTITION BY <Column_Name>)
USE AdventureWorks2012;
GO
SELECT ROW_NUMBER() OVER (ORDER BY HE.BusinessEntityID) ‘SR.NO’,
HE.BusinessEntityID,HE.Gender,HD.DepartmentID FROM HumanResources.Employee HE
JOIN HumanResources.EmployeeDepartmentHistory HEDH
ON HE.BusinessEntityID=HEDH.BusinessEntityID
JOIN HumanResources.Department HD
ON HEDH.DepartmentID=HD.DepartmentID

SELECT ROW_NUMBER() OVER (PARTITION BY HE.BusinessEntityID ORDER BY HD.DepartmentID) ‘SR.NO’,
HE.BusinessEntityID,HE.Gender,HD.DepartmentID FROM HumanResources.Employee HE
JOIN HumanResources.EmployeeDepartmentHistory HEDH
ON HE.BusinessEntityID=HEDH.BusinessEntityID
JOIN HumanResources.Department HD
ON HEDH.DepartmentID=HD.DepartmentID

SELECT SP.*
FROM Sales.SalesPerson SP
JOIN
Person.Person PP
ON SP.BusinessEntityID=PP.BusinessEntityID
JOIN Person.[Address] PA
ON PA.AddressID=PP.BusinessEntityID
GO
SELECT PP.FirstName,PP.LastName,PA.PostalCode,

ROW_NUMBER() OVER(ORDER BY PA.PostalCode) AS’ROW NUMBER’,
RANK() OVER (ORDER BY PA.PostalCode) AS ‘RANK’ ,
DENSE_RANK() OVER(ORDER BY PA.PostalCode) AS ‘DENSE RANK’,
NTILE(4) OVER(ORDER BY PA.PostalCOde) AS ‘NTILE’
FROM Sales.SalesPerson SP
JOIN
Person.Person PP
ON SP.BusinessEntityID=PP.BusinessEntityID
JOIN Person.[Address] PA
ON PA.AddressID=PP.BusinessEntityID

 
Resultset:

Most of the content of this article is taken from BOL.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s