The FIRST_VALUE() Function

The FIRST_VALUE() function is a window function in SQL that returns the first value of a column within a specified window or partition. It retrieves the first value from a set of ordered rows based on the criteria in the ORDER BY clause.

Syntax

FIRST_VALUE(column_name) OVER (
    PARTITION BY partition_column
    ORDER BY ordering_column [ASC | DESC]
)

Key Components

1. column_name: The column whose first value you want to retrieve.
2. PARTITION BY: Divides the data into groups (optional). The function is applied independently to each group.
3. ORDER BY: Specifies the order of rows within each partition. The "first value" is determined after ordering.

Example Use Case

Dataset

client  agency  assessment_#  score
123     abc     1             5
123     abc     2             6
123     abc     3             10
123     abc     4             90
456     xyz     1             8
456     xyz     2             12
456     xyz     3             15

Query: Retrieve the First Score for Each Client

SELECT
    client,
    agency,
    assessment_#,
    score,
    FIRST_VALUE(score) OVER (PARTITION BY client ORDER BY assessment_# ASC) AS first_score
FROM assessments;

Result

client  agency  assessment_#  score  first_score
123     abc     1             5      5
123     abc     2             6      5
123     abc     3             10     5
123     abc     4             90     5
456     xyz     1             8      8
456     xyz     2             12     8
456     xyz     3             15     8

Why Use FIRST_VALUE()?

• It efficiently retrieves specific values (such as first, last, or most recent) from ordered datasets.
• It simplifies queries by eliminating the need for additional joins or subqueries.