MySQL FIRST_VALUE Function

The FIRST_VALUE() function in MySQL is a window function introduced in MySQL 8.0. It returns the first value in an ordered set of rows within a partition. This function is particularly useful when you want to retrieve the first value of a group, based on specific sorting criteria.

Syntax

FIRST_VALUE(expression) OVER (
    [PARTITION BY column_name]
    [ORDER BY column_name]
)

expression: The column or value you want to retrieve.
PARTITION BY: Divides the result set into partitions; the function operates within each partition independently.
ORDER BY: Specifies the order in which rows are processed within each partition.

Key Points

FIRST_VALUE() always retrieves the first value in the ordered result set.
The PARTITION BY clause groups rows into partitions.
The ORDER BY clause determines the row order for evaluating the first value.
If no PARTITION BY clause is specified, the function treats the entire result set as a single partition.

Examples

Example 1: Basic Usage

Suppose you have a table sales:

sale_id	region	amount
1	North	100
2	North	200
3	North	150
4	South	300
5	South	250

To get the first sale amount for each region:

SELECT sale_id, region, amount,
       FIRST_VALUE(amount) OVER (PARTITION BY region ORDER BY amount) AS first_sale_amount
FROM sales;

Output:

sale_id	region	amount	first_sale_amount
1	North	100	100
3	North	150	100
2	North	200	100
5	South	250	250
4	South	300	250

Example 2: Without `PARTITION BY`

To get the overall first sale amount, regardless of region:

SELECT sale_id, region, amount,
       FIRST_VALUE(amount) OVER (ORDER BY amount) AS first_sale_amount
FROM sales;

Output:

sale_id	region	amount	first_sale_amount
1	North	100	100
3	North	150	100
2	North	200	100
5	South	250	100
4	South	300	100

Example 3: Using `FIRST_VALUE()` with Custom Order

To get the first sale amount for each region, ordered by sale ID:

SELECT sale_id, region, amount,
       FIRST_VALUE(amount) OVER (PARTITION BY region ORDER BY sale_id) AS first_sale_by_id
FROM sales;

Output:

sale_id	region	amount	first_sale_by_id
1	North	100	100
3	North	150	100
2	North	200	100
4	South	300	300
5	South	250	300

Example 4: Combine with Other Functions

You can combine FIRST_VALUE() with other window functions for deeper analysis. For example, calculate the difference between each sale and the first sale amount for each region:

SELECT sale_id, region, amount,
       FIRST_VALUE(amount) OVER (PARTITION BY region ORDER BY amount) AS first_sale_amount,
       amount - FIRST_VALUE(amount) OVER (PARTITION BY region ORDER BY amount) AS difference
FROM sales;

Output:

sale_id	region	amount	first_sale_amount	difference
1	North	100	100	0
3	North	150	100	50
2	North	200	100	100
5	South	250	250	0
4	South	300	250	50

Use Cases

Top Performers:
- Identify the first or best result in a group.
Historical Context:
- Retrieve the earliest data point for comparisons.
Trend Analysis:
- Analyze changes relative to the initial value.

Conclusion

The FIRST_VALUE() function is a powerful tool for retrieving the first value in a partition or the entire result set. By leveraging PARTITION BY and ORDER BY, you can customize its behavior for various analytical use cases, such as identifying top performers, analyzing trends, or calculating differences from the first value.

Souy Soeng

MySQL FIRST_VALUE Function