Hi Tom,

My table is as follows :

CREATE TABLE GAK
(
CURR VARCHAR2(3 BYTE),
POS NUMBER,
LCYPOS NUMBER,
DEALNO NUMBER,
DDATE DATE,
CTIMESTAMP DATE,
CONPOS NUMBER
)

Inserts into this table are done via a different select statement, but for the requirement some of the inserts are reporduced below with data:

SQL> select * from gak ;

no rows selected

SQL> Insert into gak (CURR, POS, LCYPOS, DEALNO, DDATE, CTIMESTAMP, CONPOS)
Values ('AED', 100, 1000, null, TO_DATE('09/18/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('09/18/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 100);
2
1 row created.

SQL> Insert into gak (CURR, POS, LCYPOS, DEALNO, DDATE, CTIMESTAMP, CONPOS)
Values ('AED', 50, 500, 1, TO_DATE('09/19/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('09/19/2007 15:33:51', 'MM/DD/YYYY HH24:MI:SS'), 150);
2
1 row created.

SQL> Insert into gak (CURR, POS, LCYPOS, DEALNO, DDATE, CTIMESTAMP, CONPOS)
Values ('AED', -200, -2000, 2, TO_DATE('09/19/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('09/19/2007 15:57:20', 'MM/DD/YYYY HH24:MI:SS'), -50);
2
1 row created.

SQL> Insert into gak (CURR, POS, LCYPOS, DEALNO, DDATE, CTIMESTAMP, CONPOS)
Values ('XAU', 200, 2000, NULL, TO_DATE('09/18/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('09/18/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 200);
2
1 row created.

SQL> Insert into gak (CURR, POS, LCYPOS, DEALNO, DDATE, CTIMESTAMP, CONPOS)
Values ('XAU', -500, -5000, 3, TO_DATE('09/19/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('09/19/2007 09:58:12', 'MM/DD/YYYY HH24:MI:SS'), -300);
2
1 row created.

SQL> Insert into gak (CURR, POS, LCYPOS, DEALNO, DDATE, CTIMESTAMP, CONPOS)
Values ('XAU', 600, 6000, 4, TO_DATE('09/19/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('09/19/2007 09:59:56', 'MM/DD/YYYY HH24:MI:SS'), 300);
2
1 row created.

SQL> commit ;

Commit complete.

SQL> select * from gak ;

CURR POS LCYPOS DEALNO DDATE CTIMESTAMP CONPOS
---- -------- ---------- -------- ----------- -------------------- ----------
AED 100 1000 18-SEP-2007 18-SEP-2007 00:00:00 100
AED 50 500 1 19-SEP-2007 19-SEP-2007 15:33:51 150
AED -200 -2000 2 19-SEP-2007 19-SEP-2007 15:57:20 -50
XAU 200 2000 18-SEP-2007 18-SEP-2007 00:00:00 200
XAU -500 -5000 3 19-SEP-2007 19-SEP-2007 09:58:12 -300
XAU 600 6000 4 19-SEP-2007 19-SEP-2007 09:59:56 300

6 rows selected.

Basically, the first row for each "CURR" is the closing balance of the previous day (note the previous day's date) and as and when a new row is inserted, the value of the "CONPOS" field is the sum of the previous record's "CONPOS" value with the value of the new record's "POS" value.

The requirement is to have the "CURR"-wise max(abs("CONPOS")) value for the min(timestamp)
(ie) the required output would look like the following:

CURR CONPOS
---- ------
AED 150
XAU -300

One query I tried as follows :

SQL> select distinct Curr, ConPos
2 from gak g
3 where abs(ConPos) = (select max(abs(ConPos))
4 from gak
5 where curr = g.curr)
6 order by curr ;

CURR CONPOS
---- ----------
AED 150
XAU -300
XAU 300

AS you can see, we get 2 records for XAU which is obviously incorrect. How do I use the min(timestamp) here?

Thanx in advance

ps$tkyte%ORA10GR2> select *
  2    from (
  3  select g.*, row_number() over (partition by curr order by ctimestamp) rn
  4    from (
  5  select gak.*,
  6         max( abs(conpos) ) over (partition by curr) max_conpos
  7    from gak
  8         ) g
  9   where abs(conpos) = max_conpos
 10         )
 11   where rn = 1
 12  /

CUR        POS     LCYPOS     DEALNO DDATE                CTIMESTAMP               CONPOS MAX_CONPOS         RN
--- ---------- ---------- ---------- -------------------- -------------------- ---------- ---------- ----------
AED         50        500          1 19-sep-2007 00:00:00 19-sep-2007 15:33:51        150        150          1
XAU       -500      -5000          3 19-sep-2007 00:00:00 19-sep-2007 09:58:12       -300        300          1