web 2.0

Ranking Functions in SQL SERVER 2008


Introduction

Ranking functions are used to provide simple analytics such as statistical ordering or segmentation. In this Article, you learn how to use the four ranking functions that ship with SQL Server 2008.


What Ranking Functions are available in SQL Sever?

T-SQL has four functions that can be used for ranking data:

What is ROW_NUMBER function?

The ROW_NUMBER function assigns a number from 1 to n based on a user-specified sorting order. ROW_NUMBER does not account for ties within the result set, so if you have rows with the same values within the column(s) that you are ordering by, repeated calls to the database for the same result set can produce different row numbering.

T-SQL Syntax for ROW_NUMBER


ROW_NUMBER() OVER (ORDER BY <Column_Name>) 

ROW_NUMBER() OVER (PARTITION BY <Column_Name>)


Examples of ROW_NUMBER

The following examples returns the salesperson along with his or her year-to-date sales and is numbered in descending order according to the year-to-date sales amount:


Using ROW_NUMBER function with ORDER BY Clause

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,ROW_NUMBER() OVER (ORDER BY s.SalesYTD) AS 'Rank'
    ,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;

RESULT:


Using ROW_NUMBER function with PARTITION BY Clause

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,ROW_NUMBER() OVER (PARTITION BY a.PostalCode ORDER BY s.SalesYTD) AS 'Rank'
    ,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;

RESULT:


What is RANK function?

If you need to number a result set but also deal with ties, you can use the RANK function. If the result set does not have any ties, RANK produces the same results as ROW_NUMBER.

However, if there are ties, RANK assigns the same value to each row that is tied and then skips to the next value, leaving a gap in the sequence corresponding to the number of rows that were tied.

T-SQL Syntax for RANK


RANK() OVER (ORDER BY <Column_Name>) 

RANK() OVER (PARTITION BY <Column_Name>)


Examples of RANK

The following examples show how RANK is applied to duplicates as well as within each aggregate grouping:


Using RANK function with ORDER BY Clause

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,RANK() OVER (ORDER BY s.SalesYTD) AS 'Rank'
    ,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;

RESULT:


Using RANK function with PARTITION BY Clause

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,RANK() OVER (PARTITION BY a.PostalCode ORDER BY s.SalesYTD) AS 'Rank'
    ,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;

RESULT:


What is DENSE_RANK function?

If you do not want any gaps in a sequence, you can use the DENSE_RANK function. DENSE_ RANK assigns the same value to each duplicate but does not produce gaps in the sequence.

T-SQL Syntax for DENSE_RANK


DENSE_RANK() OVER (ORDER BY <Column_Name>) 

DENSE_RANK() OVER (PARTITION BY <Column_Name>)


Examples of DENSE_RANK

The following examples show how DENSE_RANK is applied to duplicates as well as within each aggregate grouping:


Using DENSE_RANK function with ORDER BY Clause

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,DENSE_RANK() OVER (ORDER BY s.SalesYTD) AS 'Rank'
    ,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;

RESULT:


Using DENSE_RANK function with PARTITION BY Clause

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,DENSE_RANK() OVER (PARTITION BY a.PostalCode ORDER BY s.SalesYTD) AS 'Rank'
    ,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;

RESULT:


What is NTILE function?

NTILE is used to divide a result set into approximately equal groups. For example, if you wanted to split a result set into six groups with approximately the same number of rows in each group, you could use NTILE(6).

T-SQL Syntax for NTILE


NTILE(integer_expression) OVER (ORDER BY <Column_Name>) 

NTILE(integer_expression) OVER (PARTITION BY <Column_Name>)


Examples of NTILE

The following examples show how NTILE can be used to segment a result set:


Using NTILE function with ORDER BY Clause

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,NTILE(4) OVER (ORDER BY s.SalesYTD) AS 'Rank'
    ,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;

RESULT:


Using NTILE function with PARTITION BY Clause

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,NTILE(4) OVER (PARTITION BY a.PostalCode ORDER BY s.SalesYTD) AS 'Rank'
    ,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;

RESULT:


References

MSDN - SQL Server 2008

Tags: , , , , , , , , ,

SQL Server