FIRST_VALUE

The FIRST_VALUE function in Oracle is an analytic function that returns the first value in an ordered set of rows. It can be particularly useful when you want to retrieve the first row’s value within a specific partition or based on a certain sorting order. This function allows you to work with a window of rows without needing to perform a self-join or subquery.

Here's a detailed overview of the FIRST_VALUE function in Oracle:

 

1. What is the FIRST_VALUE Function?

The FIRST_VALUE function is used to return the first value in an ordered result set or partition. It looks at the first row within the window specified by the OVER clause and can return that value across multiple rows in the result set, based on the sorting criteria.

 

2. Basic Syntax of the FIRST_VALUE Function

FIRST_VALUE(expression)

    OVER (PARTITION BY partition_expression ORDER BY order_expression)

• expression: The column or expression whose value you want to retrieve.
• PARTITION BY partition_expression (optional): Divides the result set into partitions. The function operates within each partition separately.
• ORDER BY order_expression: Specifies the order in which the rows are processed to determine which row is considered the first.

 

3. How Does FIRST_VALUE Work?

The FIRST_VALUE function retrieves the value of the specified column in the first row of the result set or partition, as determined by the ORDER BY clause. Unlike aggregation functions, FIRST_VALUE does not collapse rows into a single result. Instead, it applies to each row in the result set and returns the first row’s value (based on sorting) in the window.

 

4. Example 1: Basic Usage of FIRST_VALUE

Consider the following Sales table:

Salesperson	SalesAmount	SaleDate
John	500	2025-01-01
Alice	600	2025-01-02
Bob	450	2025-01-03
Carol	700	2025-01-04

To get the first sales amount based on the sales date for each salesperson:

SELECT Salesperson, SaleDate, SalesAmount,

       FIRST_VALUE(SalesAmount) OVER (ORDER BY SaleDate) AS First_SalesAmount

FROM Sales;

Result:

Salesperson	SaleDate	SalesAmount	First_SalesAmount
John	2025-01-01	500	500
Alice	2025-01-02	600	500
Bob	2025-01-03	450	500
Carol	2025-01-04	700	500

• First_SalesAmount shows the first SalesAmount based on the SaleDate (which is 500 for the earliest sale on 2025-01-01). This value is repeated for all rows, as it is the first value in the ordered result set.

 

5. Example 2: Using FIRST_VALUE with PARTITION BY

You can partition the data by a certain column to apply the FIRST_VALUE function within each partition. For example, if you have sales data for multiple regions and want to get the first sales amount for each region, you can do the following:

SELECT Salesperson, Region, SaleDate, SalesAmount,

       FIRST_VALUE(SalesAmount) OVER (PARTITION BY Region ORDER BY SaleDate) AS First_SalesAmount

FROM Sales;

Result:

Salesperson	Region	SaleDate	SalesAmount	First_SalesAmount
John	East	2025-01-01	500	500
Alice	East	2025-01-02	600	500
Bob	West	2025-01-03	450	450
Carol	West	2025-01-04	700	450

• In this example, FIRST_VALUE is calculated within each region (East and West). The first sale for the East region is 500, and for the West region, it is 450.

 

6. Example 3: Using FIRST_VALUE with a Default Value

The FIRST_VALUE function allows you to specify a default value to return in case there are no rows in the partition (e.g., for an empty dataset). Here’s an example that returns a default value of 0 when no data exists:

SELECT Salesperson, SaleDate, SalesAmount,

       FIRST_VALUE(SalesAmount, 0) OVER (ORDER BY SaleDate) AS First_SalesAmount

FROM Sales;

Result:

Salesperson	SaleDate	SalesAmount	First_SalesAmount
John	2025-01-01	500	500
Alice	2025-01-02	600	500
Bob	2025-01-03	450	500
Carol	2025-01-04	700	500

• The default value here is not needed, as the function works in the same way as before. However, if the result set had been empty, 0 would have been returned instead of NULL.

 

7. When Should I Use FIRST_VALUE?

Here are some common scenarios where you would use FIRST_VALUE:

• Finding the first entry in a dataset: If you want to compare the current row’s value with the first row in a specific order.
• Time series analysis: To compare each data point with the first data point in a time series.
• Grouping data: When you need the first value for each group, especially when working with partitions.
• Tracking initial values: For instance, in calculating changes from an initial state (e.g., the first sales in a period).

 

8. Performance Considerations

• Windowing: Since FIRST_VALUE is an analytic function, it works over a window of rows. Large datasets can slow down performance, especially if you are using it with complex ORDER BY clauses.
• Indexing: For better performance, consider indexing the columns used in the ORDER BY and PARTITION BY clauses.
• Sorting: Sorting is crucial when using FIRST_VALUE, as it determines which row is considered the "first." If sorting is not defined, results may be inconsistent.

 

9. Comparison with RANK, DENSE_RANK, and ROW_NUMBER

Unlike RANK, DENSE_RANK, and ROW_NUMBER, which assign a rank or number to each row based on its position in the ordered set, FIRST_VALUE returns the value of the first row in the specified window (partition and order) and repeats that value for all rows in the window.

 

10. Handling NULL Values

If the first row in the ordered set contains NULL, FIRST_VALUE will return NULL for all rows in the result set. If you want to replace NULL with a default value, you can use COALESCE:

SELECT Salesperson, SaleDate, SalesAmount,

       COALESCE(FIRST_VALUE(SalesAmount) OVER (ORDER BY SaleDate), 0) AS First_SalesAmount

FROM Sales;

 

11. FIRST_VALUE vs FIRST

In Oracle, FIRST_VALUE is the correct analytic function to use to retrieve the first row’s value within a specified window. There is no FIRST function in Oracle SQL for this purpose, although it may be a function in other database systems like SQL Server or MySQL.

 

Conclusion

The FIRST_VALUE function is a powerful tool for retrieving the first value in an ordered set of rows, especially when working with partitions or ordered datasets. It allows for comparisons across rows without needing to write complex joins or subqueries. With proper use of the PARTITION BY and ORDER BY clauses, FIRST_VALUE becomes essential for time series analysis, trend analysis, and other use cases requiring access to the first value in a set of rows.

Let me know if you need more information or examples!