472,328 Members | 1,653 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 472,328 developers and data experts.

Windowing Function - 2

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

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: domeceo | last post by:
can anyone tell me why I cannot pass values in a setTimeout function whenever I use this function it says "menu is undefined" after th alert. ...
5
by: phil_gg04 | last post by:
Dear Javascript Experts, Opera seems to have different ideas about the visibility of Javascript functions than other browsers. For example, if I...
2
by: laredotornado | last post by:
Hello, I am looking for a cross-browser way (Firefox 1+, IE 5.5+) to have my Javascript function execute from the BODY's "onload" method, but if...
31
by: darwinist | last post by:
*With manual. http://darwinist.googlepages.com/htmldesktop.html Please criticise or contribute.
3
by: darwinist | last post by:
http://darwinist.googlepages.com/htmldesktop.html What's the point of something like this? As a php programmer, everything is already there for...
3
by: jefftyzzer | last post by:
Friends: Say I have a simple table like so: REGION (CHAR(3) SSN CHAR(9) ID SMALLINT With the following data:
0
debasisdas
by: debasisdas | last post by:
WINDOWING FUNCTION:- ==================== ORACLE'S WINDOWING FUNCTIONS ALLOW AGGERGATES TO BE CALCULATED FOR EACH ROW IN A RESULT SET BASED ON A...
0
by: jefftyzzer | last post by:
Friends: In v8 or v9 LUW, if I were to write (in Java or C) my own user-defined aggregate function, would I be able to use it in an OLAP...
0
by: tammygombez | last post by:
Hey fellow JavaFX developers, I'm currently working on a project that involves using a ComboBox in JavaFX, and I've run into a bit of an issue....
0
by: tammygombez | last post by:
Hey everyone! I've been researching gaming laptops lately, and I must say, they can get pretty expensive. However, I've come across some great...
0
better678
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
0
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
0
by: CD Tom | last post by:
This happens in runtime 2013 and 2016. When a report is run and then closed a toolbar shows up and the only way to get it to go away is to right...
0
jalbright99669
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
1
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.