LAST_VALUE Function

At my site there are few audit reports that display a given percentage of changes. For example, report to List 0.5 percentage of new invoices created or List 20 percent of supplier data changes such as vendor, site or payment method changes. I noticed that these reports were running a query to get total changed records and then running same query with one additional where clause like

AND rownum <= total_rows_returned_by_qury * input percentage

I thought there has to be some better way and i found analytic function LAST_VALUE.

Suppose you want to list 5 percentage of suppliers whose name like 'JOHN%', we can use LAST_VALUE function as given below

SELECT * FROM ( SELECT vendor_id , rownum rw , last_value(seq) OVER (ORDER BY seq ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS total_rows FROM ( SELECT vendor_id , vendor_name , rownum seq FROM ap_suppliers WHERE vendor_name like 'JOHN%' ) ) WHERE rownum <= total_rows * .05;

Oracle PL/SQL Programming By Feuerstein, Steven/ Pribyl, Bill (Google Affiliate Ad)