PostgreSQL database is used to store and manage huge data in tabular form to access it at any moment in the future. It allows the user to get an understanding of big data and its efficient manageability using the Relational Database Management System. The user can apply different functions to get better results from the PostgreSQL database.

This post demonstrates the process of using the NTH_VALUE function in PostgreSQL.

How to Use NTH_VALUE Function in PostgreSQL?

The NTH_VALUE function gets the nth row or data in the nth row of the table in the PostgreSQL Database Management System. The user can get the data in the ordered partition using the NTH_VALUE function and the result set will contain the Nth value of the table.

Syntax

To use the NTH_VALUE function in the PostgreSQL table, simply use the following syntax:

NTH_VALUE(field_name, offset) 
   OVER (
  [ORDER BY sort_expression [ASC | DESC]]
   )

The above code suggests:

- The NTH_VALUE keyword is used to call the function in PostgreSQL with its parameters list like expression and offset.
- The expression is the column name and the offset will be used to get the value of the Nth row.
- The ORDER BY clause in the NTH_VALUE function uses the sort_expression to get the result in order using the column from the table.
- The PARTITION BY clause is the optional one that can be used to divide the table using the values of the column.

Example 1: Use NTH_VALUE Function in PostgreSQL

To use the NTH_VALUE function in PostgreSQL, simply run the following command to get the data from the employee table:

SELECT * FROM employee;

The following screenshot displays the data from the employee table:

The following code uses the NTH_VALUE function in the employee table from the PostgreSQL database:

SELECT name, gender, salary,
  NTH_VALUE(name, 3) 
  OVER(ORDER BY salary
   RANGE BETWEEN 
  UNBOUNDED PRECEDING AND 
  UNBOUNDED FOLLOWING
   )
 FROM employee;

The above code contains the NTH_VALUE function in the PostgreSQL table with its arguments like column name and offset. The code selects the columns from the employee table and gets the Nth-value from the name column and the resultant column will be ordered by the salary column:

The above code displays the 3rd value from the name which is “Joe”.

Example 2: PARTITION BY Clause in NTH_VALUE Function

The following code uses the PARTITION BY clause with the NTH_VALUE function in PostgreSQL:

SELECT name, gender, salary,
  NTH_VALUE(name, 3) 
  OVER(PARTITION BY gender
   ORDER BY salary
   RANGE BETWEEN 
  UNBOUNDED PRECEDING AND 
  UNBOUNDED FOLLOWING
   )
 FROM employee;

The above code uses the PARTITION BY clause to divide the table using the gender column and salary will be the ordering column from the employee table:

That’s all about using the NTH_VALUE function in PostgreSQL with the help of examples.

Conclusion

To use the NTH_VALUE function in the PostgreSQL table, simply create and insert data in the table and apply the NTH_VALUE function to it. It uses the argument list including the column name and offset for setting the Nth_value of the column value. The function allows the use of the PARTITION BY clause to divide the table into smaller parts to apply the NTH_VALUE Function. This post demonstrated the process of using the NTH_VALUE function in the PostgreSQL database.