Oracle FIRST_VALUE

Summary: in this tutorial, you will learn how to return the first value in an ordered set of values by using the Oracle FIRST_VALUE() function.

Introduction to the Oracle FIRST_VALUE() function

The FIRST_VALUE() is an analytic function that allows you to get the first value in an ordered set of value

The following illustrates the syntax of the Oracle FIRST_VALUE() function:

FIRST_VALUE (expression) [ {RESPECT | IGNORE} NULLS ])
OVER (
    [ query_partition_clause ] 
    order_by_clause
    [frame_clause]
)Code language: SQL (Structured Query Language) (sql)

In this syntax:

expression

is an expression evaluated against the value of the first row in the window frame specified by the frame_clause.

The expression must return a single value and must not contain subqueries or other analytic functions. The FIRST_VALUE() function returns NULL if the value of the first row in the window frame evaluates to NULL unless you specify the IGNORE NULLS option. By default, the function uses RESPECT NULLS.

query_partition_clause

The query_partition_clause clause divides rows into partitions by one or more criteria to which the FIRST_VALUE() function is applied. The query_partition_clause clause is optional. If you skip it, the FIRST_VALUE() function will consider the whole result set as a single partition.

The query_partition_clause has the following form:

PARTITION BY expression1 [,expression2, ...]Code language: SQL (Structured Query Language) (sql)

order_by_clause

The order_by_clause clause specifies the order of the rows in each partition to which the FIRST_VALUE() function is applied:

ORDER BY expression1 [,expression2,...] [ASC | DESC ] [NULLS FIRST | LAST]Code language: SQL (Structured Query Language) (sql)

frame_clause

The frame_clause defines the frame of the current partition.

Oracle FIRST_VALUE() function examples

We’ll use the products table from the sample database to demonstrate the FIRST_VALUE() function:

The following example returns the product id, product name, list price, and the name of the product with the lowest price:

SELECT 
    product_id, 
    product_name,
    list_price, 
    FIRST_VALUE(product_name) 
     OVER (ORDER BY list_price) first_product
FROM 
    products
WHERE 
    category_id = 1;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

To get the lowest price product in each category, you add the query_partition_clause clause and remove the WHERE clause:

SELECT 
    product_id, 
    product_name,
    category_id,
    list_price, 
    FIRST_VALUE(product_name) 
        OVER (
            PARTITION BY category_id
            ORDER BY list_price
        ) first_product
FROM 
    products;  Code language: SQL (Structured Query Language) (sql)

This picture illustrates the partial output:

In this tutorial, you have learned how to get the first value in a set of values by using the Oracle FIRST_VALUE() function.