RANKING Functions in SQL SERVER Part I || RANK, DENSE_RANK, NTILE functions in SQL 2014
This video will provide you the details of each RANKING Function in SQL Server 2014.
RANK (Transact-SQL)
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.
Syntax
RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )
Arguments
OVER ( [ partition_by_clause ] order_by_clause)
partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. order_by_clause determines the order of the data before the function is applied. Theorder_by_clause is required. The rows or range clause of the OVER clause cannot be specified for the RANK function.
Return Types
Bigint
**********************************************************************************
DENSE_RANK (Transact-SQL)
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.
Syntax
DENSE_RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )
Arguments
partition_by_clause
Divides the result set produced by the FROM clause into partitions to which the DENSE_RANK function is applied. For the PARTITION BY syntax, seeOVER Clause (Transact-SQL).
order_by_clause
Determines the order in which the DENSE_RANK function is applied to the rows in a partition.
Return Types
bigint
**********************************************************************************
NTILE (Transact-SQL)
Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.
Syntax
NTILE (integer_expression) OVER ( [ partition_by_clause ] order_by_clause )
Arguments
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.
partition_by_clause
Divides the result set produced by the FROM clause into partitions to which the function is applied.
Determines the order in which the NTILE values are assigned to the rows in a partition. An integer cannot represent a column when the is used in a ranking function.
Return Types
Bigint
Remarks
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 example if the total number of rows is 53 and the number of groups is five, the first three groups will have 11 rows and the two remaining groups will have 10 rows each. If on the other hand the total number of rows is divisible by the number of groups, the rows will be evenly distributed among the groups. For example, if the total number of rows is 50, and there are five groups, each bucket will contain 10 rows.
NTILE is nondeterministic.