syntax and example of FIRST_VALUE function in oracle sql

The FIRST_VALUE Function in Oracle SQL / PLSQL is an analytical function and is used to return the first value in an ordered set of values.

1. If the first value in the ordered set is NULL, then FIRST_VALUE function returns NULL unless we specify IGNORE NULLS.
2. If we specify IGNORE NULLS then, FIRST_VALUE function returns the first NON NULL value in the ordered list, or NULL if the list contains all the NULL values.
3. FIRST_VALUE function can be used with and without PARTITION BY clause.

Syntax for using the FIRST_VALUE function in Oracle SQL / PLSQL is:
SELECT column(s)
,FIRST_VALUE(column [IGNORE NULLS]) OVER ([PARTITION BY column] ORDER BY column ROWS UNBOUNDED PRECEDING)
FROM table_name;

Example 1:

Using FIRST_VALUE Function with PARTITION BY and without IGNORE NULLS Clause.

Suppose we have a table named ‘employee’ as shown below:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support	5
105	Emp E	32000	Sales	10
106	Emp F	20000	Sales	5
107	Emp G	12000	Sales
108	Emp H	12000	Support

Suppose we wish to view department wise employee_id, employee_name, department and the first value of commission, in an ordered set, we can achieve the same as:

SELECT employee_id
       ,employee_name
       ,department
       ,FIRST_VALUE(commission) OVER (PARTITION BY department
                    ORDER BY employee_id DESC
                    ROWS UNBOUNDED PRECEDING) Minimum_Commission
FROM employee;

We will get the following result:

Employee_ID	Employee_Name	Department	Minimum_Commission
103	Emp C	IT	20
102	Emp B	IT	20
107	Emp G	Sales
106	Emp F	Sales
105	Emp E	Sales
101	Emp A	Sales
108	Emp H	Support
104	Emp D	Support

Here we can see that we have fetched first value for the commission column department wise in descending order of ‘employee_id’, for ‘IT’ department the ‘employee_id = 103’ has 20 commission and hence is fetched against ‘IT’ department.

For ‘Sales’ and ‘Support’ departments the ‘employee_id = 107’ and ‘108’ have NULL as commission, hence NULL is fetched as first value against ‘Sales’ and ‘Support’ departments.

Example 2:

Using FIRST_VALUE Function with PARTITION BY and IGNORE NULLS Clause.

Suppose we have a table named ‘employee’ as shown below:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support	5
105	Emp E	32000	Sales	10
106	Emp F	20000	Sales	5
107	Emp G	12000	Sales
108	Emp H	12000	Support

Suppose we wish to view department wise employee_id, employee_name, department and the first value of commission, in an ordered set, we can achieve the same as:

SELECT employee_id
       ,employee_name
       ,department
       ,FIRST_VALUE(commission IGNORE NULLS) OVER (PARTITION BY department
                    ORDER BY employee_id DESC
                    ROWS UNBOUNDED PRECEDING) Minimum_Commission
FROM employee;

We will get the following result:

Employee_ID	Employee_Name	Department	Minimum_Commission
103	Emp C	IT	20
102	Emp B	IT	20
107	Emp G	Sales
106	Emp F	Sales	5
105	Emp E	Sales	5
101	Emp A	Sales	5
108	Emp H	Support
104	Emp D	Support	5

Here we can see that we have fetched first values for the commission column department wise in descending order of ‘employee_id’, for ‘IT’ department the ‘employee_id = 103’ has 20 commission and hence is fetched against ‘IT’ department.

Also observe that for ‘employee_id = 107’ in Sales department the first value encountered is NULL, but the next NON NULL value is 5 and hence 5 is fetched for other employees of ‘Sales’ department.

Similarly for ‘Support’ department, ‘employee_id = 108’ has NULL value for ‘commission’ but the next NON NULL value is 5 and hence 5 is fetched for other employees of ‘Support’ department.