In PostgreSQL, ROW_NUMBER(), RANK(), and DENSE_RANK() are window functions that are used to assign unique numbers to rows within a partition of a result set. They are often used in combination with the OVER() clause to define the partitioning and ordering of the result set.
ROW_NUMBER(): This function will just rank all selected rows in an ascending order, This function gives a unique row number to each row. When used along with partition by it will give unique row number within each group.
RANK(): This function assigns ranks, but skips the rank when there are ties. When used along with partition by it will give rank within each group.
DENSE_RANK(): This function assigns ranks without skipping numbers when there are ties. When used along with partition by it will give dense rank i.e. without skipping ranks within each group.
The table BASKET which we use for depiction is shown below
ROW_NUMBER(), RANK() and DENSE_RANK() in PostgreSQL
ROW_NUMBER() function Example:
This is the simplest of all. This function will just rank all selected rows in an ascending order,
Select *, ROW_NUMBER() OVER(ORDER BY price) from basket;
Based on the price the row number is assigned on the ascending order to the resultant table will be
Output:
Rank() function Example:
RANK in PostgreSQL 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(ORDER BY price) from basket;
So the resultant table with rank is
Output:
DENSE_RANK in PostgreSQL
DENSE_RANK in PostgreSQL assigns the rank for each group. Ranks are assigned without gap so the ranking will not be skipped.
select *, DENSE_Rank() OVER(ORDER BY price) from basket;
So the resultant table with dense_rank is
Output:
ROW_NUMBER along with PARTITION BY in PostgreSQL
ROW_NUMBER() assigns a unique sequential integer to rows within a partition (Group).
select *, ROW_NUMBER () OVER (PARTITION BY item_group ORDER BY price) from Basket;
So the resultant table with row_number is assigned to Each groups like “Dairy” , “Fruits” and “Vegetable”. All these groups will get its own row_number()
Output:
RANK() along with PARTITION BY in PostgreSQL
RANK in PostgreSQL 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 assigned to Each groups like “Dairy” , “Fruits” and “Vegetable”. All these groups will get its own rank within the group.
Output:
DENSE_RANK along with PARTITION BY in PostgreSQL
DENSE_RANK in PostgreSQL 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 dense_rank is assigned to Each groups like “Dairy” , “Fruits” and “Vegetable”. All these groups will get its dense rank within the group.
Output:
