Oracle FIRST_VALUE function

Preamble

The Oracle/PLSQL FIRST_VALUE function returns the first value in an ordered set of values from the analytical window. It shares some similarities with FIRST_VALUE and NTH_VALUE.

Oracle/PLSQL syntax of FIRST_VALUE function

FIRST_VALUE (_id)
[RESPECT NULLS | IGNORE NULLS]
OVER ([query_partition_clause_id] [order_by_clause_id])

The following syntax is also the accepted format:

FIRST_VALUE (_id [RESPECT NULLS | IGNORE NULLS])
OVER ([query_partition_clause_id] [order_by_clause_id])

where:

• expression_id – The column or expression whose first value you wish to return.
• If this parameter is omitted, the default value is RESPECT NULLS, which includes NULL values, and it controls whether NULL values are enabled or ignored in the analysis window.
• The optional query_partition_clause_id allows you to categorize results based on one or more expressions.
• Ordering the data in each section is accomplished using the optional order_by_clause_id field.
• The rows in the analysis window for evaluation are specified by the optional windowing_clause_id; it is essential to use the correct windowing_clause to prevent getting unexpected results.
• Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

DDL / DML for examples

If you want to follow this guide, use the DDL to create the table and DML to fill in the data. Then try the examples in your own database!

DDL / DML for FIRST_VALUE

If you have an Oracle database and want to see what the FIRST_VALUE analytics function can do, you can find the DDL and DML you need below.

Simply adhere to the directions to finish your database. Then click the link to go back to the FIRST_VALUE training materials and practice the examples there.

Example DDL

The instructions required to create the tables used in the FIRST_VALUE example are known as DDL, or data definition language.

Observe the DDL guidelines below in your Oracle database:

CREATE TABLE empls
( empl_id number(6) NOT NULL,
f_name varchar2(25) NOT NULL,
l_name varchar2(30) NOT NULL,
salary_id number(8,2),
depart_id number(4),
CONSTRAINT emp_id_pk1 PRIMARY KEY (empl_id);

DML example

A DML is a language used for data manipulation. You will need to run these INSERT commands in your Oracle database to add the data:

In your Oracle database, execute the following DML operations:

insert into empls (empl_id, f_name, l_name, salary_id, depart_id)
(100, 'Anita', 'Borg', 2500.10);

insert into empls (empl_id, f_name, l_name, salary_id, depart_id)
(200, "Alfred", "Aho", 3200.10);

insert into empls (empl_id, f_name, l_name, salary_id, depart_id)
(300, "Bill", "Gates", 2100.10);

insert into empls (empl_id, f_name, l_name, salary_id, depart_id)
(400, 'Linus', 'Torvalds', 3700.20);

insert into empls (empl_id, f_name, l_name, salary_id, depart_id)
(500, "Michael", "Dell", 3100.20);

insert into empls (empl_id, f_name, l_name, salary_id, depart_id)
(600, "Nello", "Cristianini", 2950, 20);

insert into empls (empl_id, f_name, l_name, salary_id, depart_id)
(700, "Rasmus", "Lerdorf", 4900.20);

insert into empls (empl_id, f_name, l_name, salary_id, depart_id)
(800, "Steve", "Jobs", 2600.30);

insert into empls (empl_id, f_name, l_name, salary_id, depart_id)
900, 'Thomas', 'Kyte', 5000.30;

Example

Let’s examine some FIRST_VALUE function examples from Oracle and learn how to use FIRST_VALUE in PLSQL.

highest wage for all personnel

Use the FIRST_VALUE function to return the highest salary in the employee table by starting with a straightforward example. Because we are analyzing the entire employees table in this example, we are not using the query_partition_clause.

In this illustration, a table of employees contains the information below:

To find the highest salary, enter the following SELECT:

SELECT DISTINCT FIRST_VALUE(salary_id)
OVER (ORDER BY salary_id DESC)
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
AS "HIGH"
FROM empls;

Your desired result should be:

In this case, FIRST_VALUE (salary_id) returns the highest salary value that was specified. ORDER BY salary DESC tells the analysis window to sort the salary data in descending order. This is done in the analysis window. To ensure that all rows are included regardless of the current row, use the parameter windowing_clause = RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Additionally, we did not need to enable query_partition_clause_id to separate the data because we only wanted the highest salary in the table.

The depart_id employee earning the most
Let’s now demonstrate how to use query_partition_clause_id with FIRST_VALUE. Let’s return the highest salary for depart_id 10 and 20 in the example below.

Enter the following SQL query using the same table:

SELECT DISTINCT depart_id, FIRST_VALUE(salary_id)
OVER (PARTITION BY depart_id ORDER BY salary_id DESC)
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
AS "HIGH"
FROM empls
WHERE depart_id in (10,20)
ORDER BY depart_id;

Here are the results you should get:

In this case, FIRST_VALUE (salary_id) returns the highest salary value that was specified. When you type PARTITION BY DEPARTMENT_ID ORDER BY DESC salary in the analysis window, the results will be split up by DEPARTMENT_ID and the salary data will be put in descending order.

Lowest salary by department_id
Let’s now demonstrate how to retrieve the lowest salaries for department_ids 10 and 20 using the FIRST_VALUE function.

Again, utilizing the data in the table, enter the following SQL query:

SELECT DISTINCT depart_id, FIRST_VALUE(salary_id)
OVER (PARTITION BY depart_id ORDER BY salary_id ASC)
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
AS "LOW"
FROM empls
WHERE depart_id in (10,20)
ORDER BY depart_id;

The outcomes you should see are as follows:

The sorting order by section in this example has been changed to PARTITION BY DEPART_ID ORDER BY salary ASC, and as a result, we now obtain the lowest salary based on DEPART_ID.

FIRST_VALUE and LAST_VALUE : Problem Solving using Analytic Functions

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It finds the root causes of complex database scalability and performance problems that affect business across a growing number of cloud, RDBMS, NoSQL, and machine learning database platforms.