FIRST_VALUE, LAST_VALUE and nth_VALUE of group in postgresql

In this section we will be using FIRST_VALUE, LAST_VALUE and nth_VALUE function in postgresql to get the first value, last value and nth value of the group by using PARTITION BY clause.

FIRST_VALUE: Returns the first value of 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.

LAST_VALUE: Returns the last value of each group based on the fields applied in PARTITION BY clause.

Nth_VALUE: Returns the nth value of each group based on the fields applied in PARTITION BY clause.

The table BASKET which we use for depiction is shown below

FIRST_VALUE in postgresql:

FIRST_VALUE in postresql returns the first value of each group

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

So the resultant table

LAST_VALUE in postgresql:

LAST_VALUE in postresql returns the last value of each group

select *, LAST_VALUE (price)  OVER (
PARTITION BY item_group
ORDER BY
price RANGE BETWEEN UNBOUNDED PRECEDING
 AND UNBOUNDED FOLLOWING
) from Basket;

So the resultant table

Nth_VALUE in postgresql:

Nth_VALUE in postresql returns the nth value of each group

select *, nth_VALUE (price,3)  OVER (
PARTITION BY item_group
ORDER BY 
price RANGE BETWEEN UNBOUNDED PRECEDING
 AND UNBOUNDED FOLLOWING
) from Basket;

So the resultant table with 3^rd value of each group will be