Windowing Function - 2

**debasisdas** · Jul 4 '07, 09:39 AM

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
---------------------

Code:

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
-----------------------

Code:

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
-----------------------

Code:

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;