MySQL FIRST_VALUE

The MySQL FIRST_VALUE function is a window function that allows you to retrieve the first value in an ordered set of rows within a partition. This function is often used in conjunction with the OVER clause to define the window frame for the operation.

Syntax

Here is the basic syntax of the FIRST_VALUE function:

FIRST_VALUE(expression) OVER (
  [PARTITION BY partition_expression, ... ]
  ORDER BY sort_expression [ASC | DESC], ...
  [ROWS {UNBOUNDED PRECEDING | value PRECEDING} | {CURRENT ROW} | {value FOLLOWING | UNBOUNDED FOLLOWING}]
)

expression: The column or expression for which you want to retrieve the first value.
PARTITION BY: Optional. It divides the result set into partitions to which the FIRST_VALUE function is applied.
ORDER BY: Specifies the order of rows within each partition.
ROWS: Defines the range of rows within the partition to which the function is applied.

Example

Now, let’s go through an example to illustrate the usage of the FIRST_VALUE function. Suppose you have a table named orders with columns order_id, customer_id, and order_amount. You want to find the first order amount for each customer based on the order date.

SELECT
  order_id,
  customer_id,
  order_amount,
  FIRST_VALUE(order_amount) OVER (PARTITION BY customer_id ORDER BY order_date ASC) AS first_order_amount
FROM
  orders;

In this example, the FIRST_VALUE function is applied to the order_amount column, partitioned by customer_id, and ordered by order_date in ascending order. The result set will include the original columns (order_id, customer_id, order_amount) along with a new column first_order_amount, which represents the first order amount for each customer.

Note that you may need to adjust the column names and table names based on your actual database schema. The PARTITION BY and ORDER BY clauses should be tailored to the specific requirements of your query.