Oracle / PLSQL: FIRST_VALUE Function

This Oracle tutorial explains how to use the Oracle/PLSQL FIRST_VALUE function with syntax and examples.

Description

The Oracle/PLSQL FIRST_VALUE function returns the first value in an ordered set of values from an analytic window. It is similar to the LAST_VALUE and NTH_VALUE functions.

Syntax

The ANSI compatible syntax for the FIRST_VALUE function in Oracle/PLSQL is:

FIRST_VALUE (expression)
 [RESPECT NULLS | IGNORE NULLS]
 OVER ([query_partition_clause] [order_by_clause [windowing_clause]])

The following syntax is also an accepted format:

FIRST_VALUE (expression
 [RESPECT NULLS | IGNORE NULLS])
 OVER ([query_partition_clause] [order_by_clause [windowing_clause]])

Parameters or Arguments

expression

The column or expression that you wish to return the first value for.

RESPECT NULLS | IGNORE NULLS

Optional. It determines whether NULL values are included or ignored in the analytic window calculation. If this parameter is omitted, the default is RESPECT NULLS which includes NULL values.

query_partition_clause

Optional. It is used to partition the results into groups based on one or more expressions.

order_by_clause

Optional. It is used to order the data within each partition.

windowing_clause

Optional. It determines the rows in the analytic window to evaluate and it is important that you use the correct windowing_clause or you could get unexpected results. It can be a value such as:

windowing_clause	Description
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW	Last row in the window changes as the current row changes (default)
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING	First row in the window changes as the current row changes
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING	All rows are included in the window, regardless of the current row

Returns

The FIRST_VALUE function returns the first value as represented by its datatype.

Applies To

The FIRST_VALUE function can be used in the following versions of Oracle/PLSQL:

• Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

DDL/DML for Examples

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!

Get DDL/DML

Example

Let's look at some Oracle FIRST_VALUE function examples and explore how to use the FIRST_VALUE function in Oracle/PLSQL.

Highest Salary for all Employees

Let's start with a simple example and use the FIRST_VALUE function to return the highest salary in the employees table. In this example, we won't require a query_partition_clause because we are evaluating across the entire employees table.

In this example, we have a table called employees with the following data:

EMPLOYEE_ID  LAST_NAME  FIRST_NAME  SALARY  DEPT_ID
-----------  ---------  ----------  ------  -------

       1000  Jackson    Joe         2000    10
       2000  Smith      Jane        3500    10
       3000  Jack   Samantha    1900    10
       4000  Reynolds   Allen       4000    20
       5000  James   Paige       3250    20
       6000  Johnson    Derek       2750    20
       7000  Nelson     Sarah       5000    30
       8000  Burke      Russell     1500    30

To find the highest salary, enter the following SELECT statement:

SELECT DISTINCT FIRST_VALUE(salary)
 OVER (ORDER BY salary DESC
       RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
       AS "HIGHEST"
FROM employees;

These are the results that you should see:

HIGHEST
-------

   5000

In this example, the FIRST_VALUE returns the highest salary value as specified by FIRST_VALUE(salary). The analytic window will sort the data by salary in descending order as specified by ORDER BY salary DESC. The RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING windowing_clause is used to ensure that all rows are included regardless of the current row.

And because we want the highest salary for the table, we did not need to include a query_partition_clause to partition the data.

Highest Salary by Dept_id

Now, let's show you how to use the FIRST_VALUE function with a query_partition_clause. In this next example, let's return the highest salary for dept_id 10 and 20.

Based on the same employees table, enter the following SQL statement:

SELECT DISTINCT dept_id, FIRST_VALUE(salary)
 OVER (PARTITION BY dept_id ORDER BY salary DESC
       RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
       AS "HIGHEST"
FROM employees
WHERE dept_id in (10,20)
ORDER BY dept_id;

These are the results that you should see:

DEPT_ID  HIGHEST
-------  -------

     10     3500
     20     4000

In this example, the FIRST_VALUE returns the highest salary value as specified by FIRST_VALUE(salary). The analytic window will partition results by dept_id and order the data by salary in descending order as specified by PARTITION BY dept_id ORDER BY salary DESC.

Lowest Salary by Dept_id

Now, let's show you how to use the FIRST_VALUE function to return the lowest salary for dept_id 10 and 20.

Again based on the data in the employees table, enter the following SQL statement:

SELECT DISTINCT dept_id, FIRST_VALUE(salary)
 OVER (PARTITION BY dept_id ORDER BY salary ASC
       RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
       AS "LOWEST"
FROM employees
WHERE dept_id in (10,20)
ORDER BY dept_id;

These are the results that you should see:

DEPT_ID  LOWEST
-------  ------

     10    1900
     20    2750

In this example, we have switched the sort order to ascending on the partition as specified by PARTITION BY dept_id ORDER BY salary ASC and now we get the lowest salary based on dept_id.