ROW_NUMBER, RANK and DENSE_RANK within group in postgresql

In this Tutorial we will be using row_number , rank and dense_rank function in postgresql to rank within the group using PARTITION BY clause.

ROW_NUMBER: Returns the sequence and unique number for each group based on the fields applied in PARTITION BY clause. If PARTITION BY is not specified, the function treats all rows of the query result set as a single group.

RANK: Similar to ROW_NUMBER function and Returns the rank of each row within the partition of a result set.

DENSE_RANK: Returns the rank of rows within the partition of a result set, without any gaps in the ranking.

The table BASKET which we use for depiction is shown below

ROW_NUMBER in postgresql:

ROW_NUMBER in postresql returns the row number for each group

select *, ROW_NUMBER () OVER (
PARTITION BY item_group
ORDER BY
price
) from Basket;

So the resultant table with row_number is

RANK in postgresql

RANK in postresql assigns the rank for each group. If value remains same then both the number will get same rank and next rank will be skipped.

select *, RANK () OVER (
PARTITION BY item_group
ORDER BY
price
) from Basket;

So the resultant table with rank is

DENSE_RANK in postgresql

DENSE_RANK in postresql assigns the rank for each group. Ranks are assigned without gap so the ranking will not be skipped.

select *, DENSE_RANK () OVER (
PARTITION BY item_group
ORDER BY
price
) from Basket;

So the resultant table with rank is