FIRST_VALUE/LAST-VALUE:-
========================
THESE AGGREGATE FUNCTIONS CAN BE USED WITH WINDOWING FUNCTIONS TO IDENTIFY THE VALUES OF THE FIRST AND LAST VALUES IN THE WINDOW.
sample ex#1
---------------------
-
SELECT HIREDATE,FIRST_VALUE(SUM(SAL))OVER (ORDER BY HIREDATE ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) PREVOUS,
-
SUM(SAL) MONTHLYSAL,
-
LAST_VALUE(SUM(SAL)) OVER (ORDER BY HIREDATE ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) NEXT,
-
AVG(SUM(SAL)) OVER (ORDER BY HIREDATE ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) ROLLING
-
FROM EMP
-
GROUP BY HIREDATE
-
ORDER BY HIREDATE;
LAG/LEAD:-
==============
THESE FUNCTIONS ALLOWS ROWS TO BE REFERENCED BY THEIR POSITION RELATIVE TO THE CURRENT ROW.THESE ARE USEFUL FOR COMPAIRING ONE ROW OF A RESULT SET WITH ANOTHER ROW OF THE SAME RESULT SET.
Sample Ex1
-----------------------
- SELECT HIREDATE,SUM(SAL) MONTHLYSAL,LAG(SUM(SAL),1) OVER( ORDER BY HIREDATE) PREVIOUS_MONTH_SAL
-
FROM EMP GROUP BY HIREDATE ORDER BY HIREDATE;
Sample Ex2
-----------------------
- SELECT HIREDATE,SUM(SAL) MONTHLYSAL,
-
LAG(SUM(SAl),1) OVER( ORDER BY HIREDATE) PREVIOUS_MONTH_SAL,
-
LEAD(SUM(SAl),1) OVER( ORDER BY HIREDATE) NEXT_MONTH_SAL
-
FROM EMP GROUP BY HIREDATE ORDER BY HIREDATE;
