FIRST_VALUE(), LAST_VALUE() and Nth_VALUE() in PostgreSQL

In this Tutorial 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.

These functions in PostgreSQL are known as window functions that returns the first value, last value and nth value of a table

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() function Example:

FIRST_VALUE() function in PostgreSQL returns the first value of each group.

 

Select *, FIRST_VALUE(price) OVER(ORDER BY price) from basket;

Based on the price column its First value is selected and displayed as the column.so the resultant table will be

Output:

LAST_VALUE() function Example:

LAST_VALUE() function in PostgreSQL returns the last value of each group.

 

Select *, LAST_VALUE(price) OVER(ORDER BY price) from basket;

Based on the price column its Last value is selected and displayed as the column.so the resultant table will be

Output:

NTH_VALUE() function Example:

NTH_VALUE() function in PostgreSQL takes the N as input and returns the nth value of each group.

 

Select *, NTH_VALUE(price,4) OVER(ORDER BY price RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) from basket;

Based on the price column its 4^th value i.e. 60 is selected and displayed as the column.  The keyword “RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING” is to make sure that nth value is displayed on the entire column

So the resultant table will be

Output:

FIRST_VALUE() along with partition by in PostgreSQL:

FIRST_VALUE  along with partition by in PostgreSQL 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() along with partition by in PostgreSQL:

LAST_VALUE()  along with partition by in PostgreSQL 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() along with partition by in postgresql:

Nth_VALUE() along with partition by returns the nth value of each group. NTH_VALUE() function takes up the column name and integer as argument . The price is the column name  for which the 3^rd value will be found for each item 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