• Skip to primary navigation
  • Skip to main content

Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

  • Home
  • HTML
    • Tags in HTML
    • HTML Attributes
  • OA Framework
    • Item in OAF
    • Regions in OAF
    • General OAF Topics
  • Oracle
    • statement
    • function
    • clause
    • plsql
    • sql
You are here: Home / Oracle / function / FIRST_VALUE Function without PARTITION BY Clause in Oracle SQL – PLSQL

FIRST_VALUE Function without PARTITION BY Clause in Oracle SQL – PLSQL

November 22, 2012 by techhoneyadmin

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 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 the 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 (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
108 Emp H Support
107 Emp G Sales
106 Emp F Sales
105 Emp E Sales
104 Emp D Support
103 Emp C IT
102 Emp B IT
101 Emp A Sales

Here we can see that we have fetched NULL values in the ‘Minimum_Commission’ column because we have sorted the list in descending order of ‘employee_id’, and for ‘employee_id = 108’ the ‘commission’ is NULL , hence NULL becomes the first value that is encountered and is therefore fetched.


Example 2:

Using FIRST_VALUE Function With 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 the 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 (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
108 Emp H Support  
107 Emp G Sales  
106 Emp F Sales 5
105 Emp E Sales 5
104 Emp D Support 5
103 Emp C IT 5
102 Emp B IT 5
101 Emp A Sales 5

Here we can see that we have fetched 5 as the commission value even though the first value encountered was NULL by using IGNORE NULLS clause in FIRST_VALUE function.


Filed Under: function Tagged With: FIRSTVALUEPLSQL, FIRST_VALUE function in oracle plsql, FIRST_VALUE function in oracle sql, how to use FIRST_VALUE function in oracle database query, how to use FIRST_VALUE function in oracle plsql, how to use FIRST_VALUE function in oracle sql, syntax and example of FIRST_VALUE function in oracle database query, syntax and example of FIRST_VALUE function in oracle plsql, syntax and example of FIRST_VALUE function in oracle sql, using FIRST_VALUE function in oracle database query, using FIRST_VALUE function in oracle plsql, using FIRST_VALUE function in oracle sql

Copyright © 2023 · Parallax Pro on Genesis Framework · WordPress · Log in