• 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 / LAST_VALUE Function with PARTITION BY Clause in Oracle SQL – PLSQL

LAST_VALUE Function with PARTITION BY Clause in Oracle SQL – PLSQL

November 22, 2012 by techhoneyadmin

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

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

Syntax for using the LAST_VALUE function in Oracle SQL / PLSQL is:

SELECT column(s)
,LAST_VALUE(column [IGNORE NULLS]) OVER ([PARTITION BY column] ORDER BY column ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM table_name;

Example 1:

Using LAST_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
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 LAST value of commission that employees get in an ordered set, we can achieve the same as:

SELECT employee_id
       ,employee_name
       ,department
       ,LAST_VALUE(commission) OVER (PARTITION BY department
                   ORDER BY employee_id DESC
                   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 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 5
104 Emp D Support 5

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

For ‘Sales’ department the ‘employee_id = 101’ have NULL as commission, hence NULL is fetched as LAST value against ‘Sales’ department.

For ‘Support’ department the ‘employee_id = 104’ has 5 as commission, hence 5 is fetched as LAST value against ‘Support’ department.


Example 2:

Using LAST_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
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 LAST value of commission that employees get in an ordered set, we can achieve the same as:

SELECT employee_id
       ,employee_name
       ,department
       ,LAST_VALUE(commission IGNORE NULLS) OVER (PARTITION BY department
                   ORDER BY employee_id DESC
                   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 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 10
106 Emp F Sales 10
105 Emp E Sales 10
101 Emp A Sales 10
108 Emp H Support 5
104 Emp D Support 5

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

Also observe that for ‘employee_id = 101’ in Sales department the LAST value encountered is NULL, but the next NON NULL value is 10 for ‘employee_id =105’ and hence 10 is fetched for ‘Sales’ department.

Similarly for ‘Support’ department, ‘employee_id = 104’ has commission of and hence 5 is fetched for other employees of ‘Support’ department.


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

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