Re: Query

From: Sultan <ssyed_at_fine.co.ae>
Date: Thu, 5 Oct 2000 08:26:04 +0400
Message-Id: <10639.118555@fatcity.com>

HI,
REMOVE THE "eff_date " FROM GROUP BY CLASS YOU CAN GET YOUR ANSWER . SULTAN

• Original Message ----- From: Deepa Inamdar <dinamdar_at_visto.com> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Wednesday, October 04, 2000 21:05 PM 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 - 23:26:04 CDT