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