Re: Query

Hi,

I think it is,

select empl_uno,rate,eff_date,last_modify from Rate group by empl_uno,rate,eff_date,last_modify having eff_date=max(eff_date);

Bye
Ravindran.

• Original Message ----- From: Documentazione Tecnica <dadostecnicos_at_hotmail.com> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Thursday, October 05, 2000 2:15 AM Subject: Re: Query

>
> Try this,
> select * from RATE
> group by empl_uno
> having eff_date=max(eff_date)
> Bye
> Ste
>
> >From: "Deepa Inamdar" <dinamdar_at_visto.com>
> >Reply-To: ORACLE-L_at_fatcity.com
> >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> >Subject: Query
> >Date: Wed, 04 Oct 2000 09:05:30 -0800
> >
> >Hi All,
> >
> > I want to write a query for Rate table. It has four columns
> >empl_uno,rate,eff_date,last_modify. It has multiple rates for single
> >employee.
> >
> >emlp_uno rate eff_date last_modify
> >-------------------------------------------------------
> >207 375.00 1/1/1990 11/23/1999 4:10:47 PM
> >207 395.00 4/22/2000 5/18/2000 3:13:01 PM
> >347 235.00 1/1/1990 11/8/1999 8:20:46 PM
> >347 235.00 4/22/2000 5/18/2000 3:27:21 PM
> >347 250.00 8/1/2000 9/25/2000 1:12:44 PM
> >375 130.00 1/1/1999 9/25/2000 1:24:44 PM
> >375 150.00 8/1/2000 9/25/2000 1:29:44 PM
> >
> >I want to return latest rates for employee for example
> >
> >emlp_uno rate eff_date last_modify
> >-------------------------------------------------------
> >207 395.00 4/22/2000 5/18/2000 3:13:01 PM
> >347 250.00 8/1/2000 9/25/2000 1:12:44 PM
> >375 150.00 8/1/2000 9/25/2000 1:29:44 PM
> >
> >I wrote query like
> >select empl_uno,rate,max(eff_date),last_modify from Rate
> >group by empl_uno,rate,eff_date,last_modify
> >
> >and I get result as
> >
> >emlp_uno rate eff_date last_modify
> >-------------------------------------------------------
> >207 375.00 1/1/1990 11/23/1999 4:10:47 PM
> >207 395.00 4/22/2000 5/18/2000 3:13:01 PM
> >347 235.00 1/1/1990 11/8/1999 8:20:46 PM
> >347 235.00 4/22/2000 5/18/2000 3:27:21 PM
> >347 250.00 8/1/2000 9/25/2000 1:12:44 PM
> >375 130.00 1/1/1999 9/25/2000 1:24:44 PM
> >375 150.00 8/1/2000 9/25/2000 1:29:44 PM
> >
> >which is not right. Please help me with this. Thanks in advance
> >
> >
> >
> >
> >
>
>___________________________________________________________________________
> >Visit http://www.visto.com/info, your free web-based communications
center.
> >Visto.com. Life on the Dot.
> >
> >--
> >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >--
> >Author: Deepa Inamdar
> > INET: dinamdar_at_visto.com
> >
> >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> >San Diego, California -- Public Internet access / Mailing Lists
> >--------------------------------------------------------------------
> >To REMOVE yourself from this mailing list, send an E-Mail message
> >to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> >the message BODY, include a line containing: UNSUB ORACLE-L
> >(or the name of mailing list you want to be removed from). You may
> >also send the HELP command for other information (like subscribing).
>
> _________________________________________________________________________
> Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
>
> Share information about yourself, create your own public profile at
> http://profiles.msn.com.
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Documentazione Tecnica
> INET: dadostecnicos_at_hotmail.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Mon Oct 09 2000 - 18:37:29 CDT