# How to Use the FIRST_VALUE() Function in PostgreSQL

> FIRST_VALUE() is one of the Window functions in Postgres that retrieves the first value in the sorted partition of a result set.

PostgreSQL provides various window functions that help us in performing calculations across the rows related to the current row. These functions serve various benefits/functionalities such as computing cumulative sums, assigning ranks to the rows within the partitions, and so on.

This write demonstrates how to use FIRST_VALUE Function in Postgres using the following outlines:

\- Applying FIRST_VALUE() Function Over a Particular Table

\- Applying FIRST_VALUE() Function Over Table Partitions

 **How to Use the FIRST_VALUE() Function in PostgreSQL?**

PostgreSQL offers several built-in window functions with different functionalities. **FIRST_VALUE()** is one of them that retrieves the first value in the sorted partition/window of a result set.
    
    
    FIRST_VALUE (exp)
    OVER (
    [PARTITION BY partition_col_list]
    [ORDER BY order_col_list]
    );

Here,

\- “exp” represents an expression that can be any valid column, expression, or subquery. It must retrieve a single value.

\- “ **[PARTITION BY partition_col_list]** ” is an optional clause that splits the result set into partitions based on columns specified in “ **partition_col_list** ”. However, if you don't use it, the stated function will consider the entire result set as one partition.

 **Sample Table**

Let’s fetch the data of a sample table named “student_details” using the following statement:
    
    
    SELECT * FROM student_details;

 **Example 1: Applying FIRST_VALUE() Function Over Entire Table**

In this example, we will apply the **FIRST_VALUE()** function on the “student_details” table:
    
    
    SELECT st_id, st_name, st_gender, st_grade,
    FIRST_VALUE(st_name) 
    OVER(
    ORDER BY st_grade
    ) 
    FROM student_details;

In the above code snippet:

\- The PARTITION BY Clause is skipped, so the FIRST_VALUE() function will consider the entire table as one partition.

\- Each partition will be sorted in default (ascending) order based on the “st_grade” column.

\- The FIRST_VALUE() function will pick the first value from the “st_name” column:

The output demonstrates that FIRST_VALUE() selects a value from the first row of the sorted table.

 **Example 2: Applying FIRST_VALUE() Function Over Table Partitions**

In this example, we will apply the **FIRST_VALUE()** function on the table’s partition instead of the entire result set:
    
    
    SELECT st_id, st_name, st_gender, st_grade,
    FIRST_VALUE(st_name) 
    OVER(
    PARTITION BY st_gender 
    ORDER BY st_grade
    ) ;
    FROM student_details;

In the above code snippet:

\- Partitions are created based on the “st_gender” column.

\- Each partition will be sorted based on the “st_grade” column.

\- The FIRST_VALUE() function will pick the first value from the “st_name” column while staying within its associated partition:

The output demonstrates that the FIRST_VALUE() function selects a value from the first row based on its partitions.

 **Conclusion**

 **FIRST_VALUE()** is one of the Window functions in Postgres that retrieves the first value in the sorted partition/window of a result set. The FIRST_VALUE() function picks the first value from the selected column while staying within its associated partition. If the PARTITION BY Clause is skipped/ignored, the FIRST_VALUE() function will consider the entire table as one partition. This write-up has demonstrated various use cases of the FIRST_VALUE() function.

---
[View this page online](https://www.commandprompt.com/education/how-to-use-the-first_value-function-in-postgresql/)