RE: Query

From: Deepa Inamdar <dinamdar_at_visto.com>
Date: Wed, 04 Oct 2000 11:37:17 -0700
Message-Id: <10639.118504@fatcity.com>

Thanks alot Kevin.

-----Original Message-----
From: Toepke, Kevin M ktoepke_at_cms.cendant.com Sent: Wed, 04 Oct 2000 10:15:47 -0800 To: ORACLE-L_at_fatcity.com
Subject: RE: Query

SELECT empl_uno, rate, eff_date, last_modif FROM rate r1
WHERE eff_date =3D (SELECT MAX(r2.eff_date)

                   FROM   rate r2
                   WHERE  r2.empl_uno =3D 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

>=20
>=20

> Hi All,

>=20


>     I want to write a query for Rate table. It has four=20

> columns empl_uno,rate,eff_date,last_modify. It has multiple=20
> rates for single employee.=20

>=20


> 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

>=20

> I want to return latest rates for employee for example

>=20


> 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

>=20

> I wrote query like=20
> select empl_uno,rate,max(eff_date),last_modify from Rate
> group by empl_uno,rate,eff_date,last_modify=20

>=20

> and I get result as

>=20


> 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

>=20

> which is not right. Please help me with this. Thanks in advance

>=20
>=20
>=20
>=20
>=20


> ______________________________________________________________

> _____________
> Visit http://www.visto.com/info, your free web-based=20
> communications center.
> Visto.com. Life on the Dot.

>=20

> --=20
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --=20
> Author: Deepa Inamdar
> INET: dinamdar_at_visto.com

>=20


> 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).
>=20
--=20
Please see the official ORACLE-L FAQ: http://www.orafaq.com --=20
Author: Toepke, Kevin M
INET: ktoepke_at_cms.cendant.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).

_________________________________________________________________________=

__
Visit http://www.visto.com/info, your free web-based communications cente= Received on Wed Oct 04 2000 - 13:37:17 CDT