FIRST_VALUE Function in Oracle

The FIRST_VALUE() function in Oracle is an analytical function that allows you to retrieve the value of a specified expression from the first row in a partition of the result set. It's commonly used to fetch the first value in a sorted set of data within each group or partition.

Here's the syntax of the FIRST_VALUE() function:

FIRST_VALUE(expression) OVER (

    [PARTITION BY partition_expression]

    ORDER BY order_expression [ASC | DESC]

    [ROWS | RANGE] [BETWEEN window_specification]

) AS alias

- expression: The column or expression whose first value you want to retrieve.

- PARTITION BY: Optional clause to partition the result set into groups. The function is applied separately to each partition.

- ORDER BY: Specifies the sorting criteria to determine the first value within each partition.

- ROWS | RANGE: Specifies the window frame for the analytical function.

- BETWEEN: Specifies the window specification, such as the range of rows or values over which to perform the calculation.

- alias: Optional alias for the result column.

Here's an example of using the FIRST_VALUE() function in Oracle:

Suppose we have a table named sales with columns product, region, and revenue. We want to retrieve the first revenue value for each product within each region. Here's how we can use the FIRST_VALUE() function:

SELECT 

    product,

    region,

    revenue,

    FIRST_VALUE(revenue) OVER (

        PARTITION BY product, region

        ORDER BY revenue DESC

    ) AS first_revenue

FROM 

    sales;

In this example:

- We partition the result set by product and region.

- We order the rows within each partition by revenue in descending order.

- The FIRST_VALUE(revenue) function retrieves the first revenue value within each partition, which corresponds to the highest revenue value for each product within each region.

- The result set includes columns for product, region, revenue, and first_revenue, where first_revenue contains the first revenue value for each group.

Let's look at the actual example:-

To demonstrate the output of the query using the FIRST_VALUE() function in Oracle, let's consider a sample table sales with the following data:

| Product | Region | Revenue |

|---------|--------|---------|

| A       | East   | 100     |

| A       | East   | 150     |

| A       | West   | 120     |

| B       | East   | 200     |

| B       | West   | 180     |

| B       | West   | 220     |

Now, let's run the SQL query:

SELECT 

    product,

    region,

    revenue,

    FIRST_VALUE(revenue) OVER (

        PARTITION BY product, region

        ORDER BY revenue DESC

    ) AS first_revenue

FROM 

    sales;

The output of the query will be:

| Product | Region | Revenue | First_Revenue |

|---------|--------|---------|---------------|

| A       | East   | 100     | 150           |

| A       | East   | 150     | 150           |

| A       | West   | 120     | 120           |

| B       | East   | 200     | 200           |

| B       | West   | 180     | 220           |

| B       | West   | 220     | 220           |

In the output:

- For each combination of product and region, the FIRST_VALUE(revenue) function retrieves the highest revenue value within that group.

- The first_revenue column displays the first revenue value for each group, which corresponds to the highest revenue value within each product and region combination.

This query allows us to identify the highest revenue value for each product within each region, providing valuable insights into sales performance.