| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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).
Share information about yourself, create your own public profile at Received on Wed Dec 31 1969 - 17:59:59 CST
 |
 |