Rank function

RANK

This window function assigns a rank to each row within the result set based on the values of one or more columns. It is commonly used to determine the ranking of rows based on a specified order.

Syntax – RANK() OVER ([PARTITION BY column name] ORDER BY Aggregate function with column name)

Example: We are assigning a ranking to the region based on sales performance.

DENSE_RANK

Window function similar to RANK(). Like RANK(), it assigns a rank to each row within the result set based on the values of one or more columns. However, unlike RANK(), DENSE_RANK() does not skip ranks in case of ties. Rows with equal values receive the same rank, and the next rank is not skipped.

Syntax – DENSE_RANK() OVER ([PARTITION BY column name] ORDER BY Aggregate function with column name)

Example: We are conducting dense ranking for the region about sales.

PERCENT_RANK

Used to calculate the relative rank of a row within a result set. It assigns a percentile rank to each row based on the ordering of values in the specified column(s). The result is a value between 0 and 1, where 0 represents the lowest rank, and 1 represents the highest rank.

Syntax – PERCENT_RANK() OVER ([PARTITION BY column name]ORDER BY Aggregate function with column name)

Example: We are executing percent ranking for the region concerning sales.