SELECT empl_uno, rate, eff_date, last_modif
FROM rate r1
WHERE eff_date = (SELECT MAX(r2.eff_date)
FROM rate r2
WHERE r2.empl_uno = r1.empl_uno)
> -----Original Message-----
> From: Deepa Inamdar [mailto:dinamdar_at_visto.com]
> Sent: Wednesday, October 04, 2000 1:05 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Query
>
>
> 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
Received on Wed Oct 04 2000 - 12:00:05 CDT
