:::: MENU ::::

Posts Categorized / Ranking Functions

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

SQL Server 2005 ranking function NTILE(n)

SQL Server 2005 ranking function NTILE(n) distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. If the number of rows in a partition is not divisible by integer_expression, this will cause groups of two sizes that differ by one member. Larger groups come before smaller groups in the order specified by the OVER clause. For each row, NTILE returns the number of the group to which the row belongs. Return type of NTILE(n) is bigint. Syntax for NTILE(n) is

 
NTILE (integer_expression) OVER ( [ ] <> )  

Integer_expression is a positive integer constant expression that specifies the number of groups into which each partition must be divided. integer_expression can be of type int, or bigint.
It divides the result set produced by the FROM clause into partitions to which the RANK function is applied.<> determines the order in which the NTILE values are assigned to the rows in a partition. The following example of NTILE() from BOL divides the rows into four groups. Because the total number of rows is not divisible by the number of groups, the first group has four rows and the remaining groups have three rows each.

 
USE AdventureWorks;  
 GO  
 SELECT c.FirstName, c.LastName  
 ,NTILE(4) OVER(ORDER BY SalesYTD DESC) AS 'Quartile'  
 ,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 &amp;lt;&amp;gt; 0  
 GO  

The following example of NTILE() adds the PARTITION BY argument to the code in example A. The rows are first partitioned by PostalCode and then divided into four groups within each PostalCode. Notice that the ORDER BY in the OVER clause orders the NTILE and the ORDER BY of the SELECT statement orders the result set.

 
USE AdventureWorks;  
 GO  
 SELECT c.FirstName, c.LastName  
 ,NTILE(4) OVER(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS 'Quartile'  
 ,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 &amp;lt;&amp;gt; 0  
 ORDER BY LastName  
 GO  
  • Nov 11 / 2008
  • 0
dbDigger, Ranking Functions, T-SQL Enhancements

SQL Server 2005 ranking function DENSE_RANK()

SQL Server 2005 ranking function DENSE_RANK() returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question. If two or more rows tie for a rank in the same partition, each tied rows receives the same rank. For example, if the two top salespeople have the same SalesYTD value, they are both ranked one. The salesperson with the next highest SalesYTD is ranked number two. This is one more than the number of distinct rows that come before this row. Therefore, the numbers returned by the DENSE_RANK function do not have gaps and always have consecutive ranks.
The sort order used for the whole query determines the order in which the rows appear in a result. This implies that a row ranked number one does not have to be the first row in the partition. Return type of DENSE_RANK() is bigint. Syntax for DENSE_RANK() is

</pre>
<pre><code>DENSE_RANK ( ) OVER ( [ <> ] <> )] <> ) </code></pre>
<pre>

<> Divides the result set produced by the FROM clause into partitions to which the DENSE_RANK function is applied. <> determines the order in which the DENSE_RANK values are applied to the rows in a partition. An integer cannot represent a column in the that is used in a ranking function. The following example of DENSE_RANK() from BOL returns the DENSE_RANK of the quantity of products at the various locations. Notice that the ORDER BY in the OVER clause orders the DENSE_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,  
 DENSE_RANK() OVER (PARTITION BY i.LocationID ORDER BY i.Quantity) AS DENSE_RANK  
 FROM Production.ProductInventory i  
 JOIN Production.Product p ON i.ProductID = p.ProductID  
 ORDER BY Name  
 GO  
  • 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
  • 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 &amp;lt;&amp;gt; 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 &lt;&gt; 0  
 GO  
  • Nov 10 / 2008
  • 0
dbDigger, Ranking Functions, T-SQL Enhancements

Ranking functions in SQL Server 2005 T-SQL

Ranking functions are introduced in SQL Server 2005. There are four ranking functions introduced in SQL Server 2005. Ranking functions are non deterministic in nature. Ranking functions can be used for tasks like generating sequential numbers, creation of arrays etc. Ranking functions use ‘partition by’ and ‘order by’ clauses. It is important to note that ‘order by’ clause is not optional while using Ranking functions.

Consult us to explore the Databases. Contact us