RE: help with query

Why don't you try...

select * from table
where (id,date) in (select id, max(date) from table group by id)

Stephen

-----Original Message-----
From: Ashish Shah [mailto:ar_shah_at_yahoo.com] Sent: 04 August 2000 17:09
To: Multiple recipients of list ORACLE-L Subject: RE: help with query

Ohh that won't work.

Here is the actual situation.

id date balance_before balance_after amount  

 1      15-jul-00    50             100           50
 1      15-jul-00     50            100           50
 1      15-jul-00     100           300           200 

 2      15-jul-00     50            200           150
 2      15-jul-00    100            300         200 

so I need to see whole row..

like
 1 15-jul-00 100 300 200

.........

Thanks.

• Jitendra Kamble <Jitendra.Kamble_at_TRIGYN.com> wrote:
  > select id,trunc(date), max(amount) from <table_name>
  > group by
  > id,trunc(date);
  >
  >
  > Jitendra Kamble,
  > Team Leader,
  > Trigyn Technologies Ltd.
  > Tele# : +91-22-5175090 x594
  >
  >
  > > -----Original Message-----
  > > From: Ashish Shah [SMTP:ar_shah_at_yahoo.com]
  > > Sent: Friday, August 04, 2000 8:12 PM
  > > To: dbalist; lazydba
  > > Subject: help with query
  > >
  > > Hi all,
  > >
  > > Here is the data..
  > >
  > > id date amount
  > >
  > > 1 15-jul-00 100
  > > 1 15-jul-00 50
  > > 1 15-jul-00 200 <<<<<<<<
  > > 2 15-jul-00 50
  > > 2 15-jul-00 100 <<<<<<<<
  > >
  > > I need to get a row with max(date) (Date field is
  > > with time so we can get last row that was
  > inserted)
  > > for each user..
  > >
  > >
  > > so my output should be
  > >
  > > 1 15-jul-00 200
  > > 2 15-jul-00 100
  > >
  > >
  > > Any idea??