Windowing ranking

Definition

The RANK() function is a window function that assigns a rank to each row within a partition of a result set. The rows within a partition that have the same values will receive the same rank. The rank of the first row within a partition is one.

Parameters

1. Partition by (+add):

   • Def: Specify the column for grouping.

2. Order by (+add):

   • Def: Specify the column for ordering.

3. Function:

   • Def: Specify the function to use.

4. N:

   • Def: Specify the size of rows.

5. Column output:

   • Def: Specify the name of the column.

Additionnal Comments

Functions:

1. rank:

   • Def: Returns a rank to each row in the partition of a result set. The rank of a row is determined by one plus the number of ranks that come before it.

2. dense_rank:

   • Def: Returns a rank to each row in the partition of a result set. The rank values are not skipped in case of ties.

3. ntile:

   • Def: Distributes rows of an ordered partition into a specified number of approximately equal groups. It assigns each group a bucket number starting from one to N.

4. percent_rank:

   • Def: Returns the percentile ranking of rows in a result set : percent_rank = (rank - 1) / (total_rows - 1).

5. row_number:

   • Def: Returns a sequential integer to each row of a query’s result set. Rows are ordered starting from one based on the order specified by the ORDER BY clause in the window definition.