472,328 Members | 1,653 Online

# Windowing Function - 2

8,127 Expert 4TB
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
---------------------
Expand|Select|Wrap|Line Numbers
1. SELECT HIREDATE,FIRST_VALUE(SUM(SAL))OVER (ORDER BY HIREDATE ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) PREVOUS,
2. SUM(SAL) MONTHLYSAL,
3. LAST_VALUE(SUM(SAL)) OVER (ORDER BY HIREDATE ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) NEXT,
4. AVG(SUM(SAL)) OVER (ORDER BY HIREDATE ROWS BETWEEN 2 PRECEDING  AND 2 FOLLOWING) ROLLING
5. FROM EMP
6. GROUP BY HIREDATE
7. ORDER BY HIREDATE;
==============
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
-----------------------
Expand|Select|Wrap|Line Numbers
1. SELECT HIREDATE,SUM(SAL) MONTHLYSAL,LAG(SUM(SAL),1) OVER( ORDER BY HIREDATE) PREVIOUS_MONTH_SAL
2. FROM EMP GROUP BY HIREDATE ORDER BY HIREDATE;
Sample Ex2
-----------------------
Expand|Select|Wrap|Line Numbers
1. SELECT HIREDATE,SUM(SAL) MONTHLYSAL,
2. LAG(SUM(SAl),1) OVER( ORDER BY HIREDATE) PREVIOUS_MONTH_SAL,
3. LEAD(SUM(SAl),1) OVER( ORDER BY HIREDATE) NEXT_MONTH_SAL
4. FROM EMP GROUP BY HIREDATE ORDER BY HIREDATE;
Jul 4 '07 #1
0 5330