create table transactions (
id integer not null,
tdate date not null,
opening_bal number(8,2) not null,
closing_bal number(8,2) not null,
transaction number(8,2) not null,
constraint pk_transactions primary key (id,tdate));
insert into transactions (id,tdate,opening_bal,closing_bal,transaction) values (1,'01-jan-10',0,10,10);
insert into transactions (id,tdate,opening_bal,closing_bal,transaction) values (1,'02-jan-10',10,20,10);
insert into transactions (id,tdate,opening_bal,closing_bal,transaction) values (1,'03-jan-10',20,25,5);
insert into transactions (id,tdate,opening_bal,closing_bal,transaction) values (1,'04-jan-10',25,-15,-40);
insert into transactions (id,tdate,opening_bal,closing_bal,transaction) values (1,'05-jan-10',-15,0,15);
insert into transactions (id,tdate,opening_bal,closing_bal,transaction) values (1,'06-jan-10',0,13,13);
SQL> select * from transactions;
ID TDATE OPENING_BAL CLOSING_BAL TRANSACTION
---------- --------- ----------- ----------- -----------
1 01-JAN-10 0 10 10
1 02-JAN-10 10 20 10
1 03-JAN-10 20 25 5
1 04-JAN-10 25 -15 -40
1 05-JAN-10 -15 0 15
1 06-JAN-10 0 13 13
6 rows selected.
SQL> select id, count(tdate) as tcount
,max(opening_bal) keep (dense_rank first order by tdate) as opening_bal
,max(closing_bal) keep (dense_rank last order by tdate) as closing_bal
from transactions
where tdate between '02-jan-10' and '04-jan-10'
group by id;
ID TCOUNT OPENING_BAL CLOSING_BAL
---------- ------------ ----------- -----------
1 3 10 -15
SQL>
Sunday, April 28, 2013
Oracle:How to get first and last rows in an aggregation
Subscribe to:
Post Comments (Atom)
Popular Posts
-
Aayiram Kannumai - Nokketha Doorathu Kannum Nattu Guitar tabs / chords for Aayiram Kannumai from the movie Noketha Doorath Kannum...
-
Music : God Almighty Direction : Manirathnam Lyrics : Vairamuthu Singer : Shakthishree Gopalan I know this is a Tamil song, but then I ...
-
Category: Malayalam Chords Written by IGH Admin Hits: 305 Song: Muthuchippi Movie: Thattathin Marayath Music: Sh...
-
Song: Nila Paithale Movie: Olympian Anthony Adam Music: Ousepachan ..D.......................G..........................
-
DataStage has two data types that can be used to store dates - DATE and TIMESTAMP. Transforming data often requires that we manipulate dat...
-
Abstract of Google Glass The emergence of Google Glass , a prototype for a transparent Heads-Up Display (HUD) worn over one eye...
-
Guitar tabs / chords for Azhalinte Azhangalil from Ayalum Njanum Thammil Song : Azhalinte Aazhangalil Movie : Ayalum Njanum Thammil (2012) ...
-
We've shown how to create DataStage jobs that can process tables and files generically. What we now need is a way to control those job...
-
orchdbutil is a command line utility that allows you to print out table table definitions. It connects to the database, queries the databa...
-
B--1--3-4--1-3-1-0-----1--3--4-1-3-0-1-------------- G-------------------0--------------------------------- thumbi vaa thumbak...
0 comments:
Post a Comment
Please Post your Comments..!