DENSE_RANK()
window function assigns a rank for each value within a specified group, based on the ORDER BY
expression in the OVER
clause. Unlike the RANK()
function, which can leave gaps in the ranking sequence when there are ties, DENSE_RANK()
provides consecutive rank values without any gaps. This function can be used with all data types supported by Oxla.
BIGINT
and it indicates the rank of values in a table, regardless of the input types. If the ORDER BY
expression is omitted, all ranks will default to 1. In case an optional PARTITION BY
expression is included, the rankings are reset for each group of rows. The rows with equal values for the ranking criteria receive the same rank.
RANK()
function, there is no gap in the sequence of ranked values (if two rows are ranked 1, the next rank will be 2)()
: this function takes no parameters, but empty parentheses is requiredPARTITION BY
: optional clause, which is used to divide the result set into partitions to which the DENSE_RANK()
function is applied (if omitted, the entire result set is treated as a single partition)ORDER BY
: order of rows in each partition to which the function is appliedwinsales
table that stores information about some sales transactions:
DENSE_RANK()
function with ORDER BY
keyword and calculate the descending dense rank of all rows based on the quantity sold:
DENSE_RANK()
function with ORDER BY
keyword and PARTITION BY
clause and partition the table by seller ID, then order each partition by the quantity and assign a dense rank to each row: