Using FIRST_VALUE to find the first occurrence of a currency exchange rate in a dataset

20 Oct 2023

When working with datasets that contain currency exchange rates, it is often necessary to find the first occurrence of a specific rate. The SQL window function FIRST_VALUE can be used to easily retrieve this information.

What is FIRST_VALUE?

FIRST_VALUE is a window function in SQL that returns the value of a specified expression along with the corresponding row from the partition. It allows you to retrieve the first value within a group of rows defined by the PARTITION BY clause.

Syntax of FIRST_VALUE

The syntax for using FIRST_VALUE is as follows:

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

expression: The column or expression you want to retrieve the first value of.
PARTITION BY: Optional clause that defines the groups of rows to consider.
ORDER BY: Specifies the order in which rows are processed within each partition.
ASC | DESC: Optional keyword to specify the sort order. By default, it is ascending.
ROWS or RANGE: Optional clauses to further define the window frame within each partition.

Example:

Let’s say we have a table called exchange_rates, which contains the following columns: date, currency, and rate. We want to find the first occurrence of the exchange rate for a specific currency.

Here’s an example of how to use FIRST_VALUE to achieve this:

SELECT
    currency,
    FIRST_VALUE(rate) OVER (
        PARTITION BY currency
        ORDER BY date ASC
    ) AS first_rate
FROM
    exchange_rates;

In this example, we are partitioning the data by currency and ordering it by date in ascending order. The FIRST_VALUE function then retrieves the first rate value within each partition.

Conclusion

FIRST_VALUE is a powerful SQL function that allows you to easily retrieve the first occurrence of a specific column within a dataset. By using this function, you can efficiently find the initial value of currency exchange rates or any other metric in your data.