A powerfull feature of the LAST_VALUE/FIRST_VALUE analytic function is the IGNORE NULL clause. This feature allows us to inherit a column value from a prio row with a specific attribute.

Just lately I came accross the following problem on ORACLE SQL & PL/SQL support forum:

The Data:

WITH data (skp_id, code_status, date_app) 
       AS (SELECT 111, 'REJECTED', DATE '2016-01-01' FROM dual UNION ALL
           SELECT 123, 'APPROVED', DATE '2016-02-01' FROM dual UNION ALL
           SELECT 201, 'APPROVED', DATE '2016-03-01' FROM dual UNION ALL
           SELECT 302, 'REJECTED', DATE '2016-04-01' FROM dual UNION ALL
           SELECT 403, 'REJECTED', DATE '2016-05-01' FROM dual UNION ALL
           SELECT 450, 'APPROVED', DATE '2016-06-01' FROM dual UNION ALL
           SELECT 475, 'APPROVED', DATE '2016-07-01' FROM dual UNION ALL
           SELECT 480, 'APPROVED', DATE '2016-08-01' FROM dual UNION ALL
           SELECT 490, 'REJECTED', DATE '2016-09-01' FROM dual) 
SELECT skp_id
     , code_status
     , date_app
 FROM data
/

The task was to show with every line the last rejection date prior to the current row as an additional column.

To achieve this the LAST_VALUE analytic function with the IGNORE NULLS addition is very handy as it allows to ignore those rows that we are not interested in (the approved ones).

---

Solution:

In a first step we nullify all rows in the generated last_rejected column that are of no interest:

DECODE(code_status,'REJECTED',date_app)

The next step searches the last entry in the result set where the column value of the generated column is not null.

LAST_VALUE (DECODE(code_status,'REJECTED',date_app) IGNORE NULLS) 
      OVER (ORDER BY skp_id)

And the last step makes sure, that we only evaluate to prior of the current row but not including the current row.

ROWS BETWEEN UNBOUNDED PRECEDING 
                            AND 1 PRECEDING

The whole statement looks like this:

WITH data (skp_id, code_status, date_app) 
       AS (SELECT 111, 'REJECTED', DATE '2016-01-01' FROM dual UNION ALL
           SELECT 123, 'APPROVED', DATE '2016-02-01' FROM dual UNION ALL
           SELECT 201, 'APPROVED', DATE '2016-03-01' FROM dual UNION ALL
           SELECT 302, 'REJECTED', DATE '2016-04-01' FROM dual UNION ALL
           SELECT 403, 'REJECTED', DATE '2016-05-01' FROM dual UNION ALL
           SELECT 450, 'APPROVED', DATE '2016-06-01' FROM dual UNION ALL
           SELECT 475, 'APPROVED', DATE '2016-07-01' FROM dual UNION ALL
           SELECT 480, 'APPROVED', DATE '2016-08-01' FROM dual UNION ALL
           SELECT 490, 'REJECTED', DATE '2016-09-01' FROM dual) 
SELECT skp_id
     , code_status
     , date_app
     , LAST_VALUE (DECODE(code_status,'REJECTED',date_app) IGNORE NULLS) 
             OVER (ORDER BY skp_id 
                   ROWS BETWEEN UNBOUNDED PRECEDING 
                            AND 1 PRECEDING) as last_rejected
 FROM data
/

Analytic functions are not really new – the came with 8.1.6 – but still they do not get the attraction they deserve.