Oracle LAG Function

LAG is an analytic function, which was introduced in Oracle 8.1.6. It gives access to multiple rows within a table, without the need for a self-join.

Syntax:
LAG ( expression [, offset [, default] ] )
   OVER ( [ query_partition_clause ] order_by_clause )

This post describes an example usage of this LAG function. PRODUCTS table with the sample data as shown in the screenshot below is assumed for this example.

Scripts to create table structure and sample data are given here.

Idea is to organize the products into categories and brands as shown in the screenshot below and show category/brand name only once for per category/brand.

This output can be achieved using LAG function, without any efforts.

SQL Code:

SELECT 
  --

  DECODE( LAG(DATA.product_category) 

            OVER (ORDER BY DATA.product_category, DATA.brand, DATA.product_name), 

        DATA.product_category, 

        NULL, 

        DATA.product_category) product_category,
  --

  DECODE( LAG(DATA.brand) 

            OVER (ORDER BY DATA.product_category, DATA.brand, DATA.product_name), 

         DATA.brand, 

         NULL, 

         DATA.brand) brand,
  --

  DATA.product_name,

  DATA.price_per_unit,

  DATA.currency,

  DATA.product_id

FROM products DATA

Read more about LAGFunction here…