First_Value, Last

Oracle Analytic Function - First_Value, Last_Value

This is an example of Oracle analytic function FIRST_VALUE OVER(ORDER/PARTTION BY ...) and LAST_VALUE OVER(ORDER/PARTTION BY ...). The value returned by FIRST_VALUE and LAST_VALUE is based on use of ORDER BY or PARTITION BY as shown below.

SELECT q_data.idx, q_data.div_cd, q_data.prod_cd, q_data.Cust_Cd,  
  COUNT(*) OVER(PARTITION BY q_data.div_cd, q_data.prod_cd, q_data.cust_cd  
  ORDER BY q_data.div_cd, q_data.prod_cd, q_data.cust_cd) Cnt,  
  FIRST_VALUE(q_data.prod_cd) OVER (ORDER BY q_data.div_cd
      ROWS UNBOUNDED PRECEDING) AS fv_order_,
  FIRST_VALUE(q_data.prod_cd) OVER (PARTITION BY q_data.div_cd) AS fv_div_,
  FIRST_VALUE(q_data.prod_cd) OVER (PARTITION BY q_data.cust_cd) AS fv_cust,
  LAST_VALUE(q_data.prod_cd) OVER (ORDER BY q_data.div_cd
      ROWS UNBOUNDED PRECEDING) AS lv_order_,
  LAST_VALUE(q_data.prod_cd) OVER (PARTITION BY q_data.div_cd) AS lv_div_,
  LAST_VALUE(q_data.prod_cd) OVER (PARTITION BY q_data.cust_cd) AS lv_cust
 FROM (SELECT 1 idx, 1001 div_cd, 5001 prod_cd, 101 cust_cd FROM DUAL UNION  
       SELECT 2 idx, 1001 div_cd, 5001 prod_cd, 101 cust_cd FROM DUAL UNION
       SELECT 3 idx, 1001 div_cd, 5002 prod_cd, 101 cust_cd FROM DUAL UNION
       SELECT 4 idx, 1002 div_cd, 5005 prod_cd, 102 cust_cd FROM DUAL UNION
       SELECT 5 idx, 1002 div_cd, 5001 prod_cd, 102 cust_cd FROM DUAL UNION
       SELECT 6 idx, 1003 div_cd, 5006 prod_cd, 101 cust_cd FROM DUAL UNION
       SELECT 7 idx, 1004 div_cd, 5003 prod_cd, 104 cust_cd FROM DUAL UNION
       SELECT 8 idx, 1004 div_cd, 5004 prod_cd, 104 cust_cd FROM DUAL) q_data;
//Complete ->  Output

Data with NULL values

Oracle registered trademark of Oracle Corporation.

Last Revised on: February 23, 2012

72897