Re: Inner join ?
Date: 2 Jul 2002 08:40:55 -0700
Message-ID: <86c750f4.0207020740.5f3d1f34_at_posting.google.com>
You could try replacing the subquery by an inline view
SELECT a.item, a.material, a.updatedate, a.material_weight FROM table_one a, ( SELECT item, material, max(updatedate) upddt
FROM table_one
GROUP BY item, material ) b
WHERE a.item = b.item
AND a.material = b.material
AND a.updatedate = b.upddt;
IF that doesnt spped up things, replace the inline view by a materialized view.
- rajXesh
"Jurriaan Themmen" <j.themmen_at_draka.nl> wrote in message news:<afrqbi$p3r$1_at_reader07.wxs.nl>...
> Daniel,
>
> I didn't use the exact Oracle datatype-descriptions, but merely indicated
> what kind of fields I'm using. (Frankly, I don't even know what kind of
> data-types Oracle has). It's not homework. I need the result of this query
> as part of a couple of other queries to calculate cost-prices....
>
> I've tried a query like this:
>
> select item, material, update, material_weight
> from table 1
> where item, material, update = (select item, material, max(update) from
> table group by item, material)
>
> but I had no joy. Either the syntax isn't correct or the query is written
> wrong because it's sooooooooooooooooo sloooooooooowww (the table contains
> more than 2 million records).
>
> I was just wondering if anyone would know a simple way to solve this. If I'd
> use Microsoft Access, I'd have the table sorted by item, material and update
> and retrieve the first or last values. Oracle doesn't seem to have a FIRST
> or LAST SQL command.
>
> TIA
>
> Jurriaan
>
>
> "Daniel Morgan" <dmorgan_at_exesolutions.com> schreef in bericht
> news:3D20745C.158ECA77_at_exesolutions.com...
> > Jurriaan Themmen wrote:
> >
> > > Hi,
> > >
> > > I have a table with the following structure:
> > >
> > > product (txt)
> > > material (txt)
> > > update (date)
> > > material_weight (num)
> > >
> > > Explanation:
> > >
> > > Every product consists of a few materials. Every time the composition of
a
> > > product is changed, the material weights are updated.
> > >
> > > How do I query this table so that I get the most recent composition per
> > > product , i.e. product, material, max(update), material_weight ?
> > >
> > > Kind regards
> > >
> > > Jurriaan
> >
> > My impression is that this is homework from a class so I will not give you
the
> > answer. Why do I think that? Because Oracle has no field types named "txt"
and
> > "num" and anyone really working with Oracle would not misstate column
> > definitions.
> >
> > The hint you do get however is this: You need a subquery.
> >
> > Daniel Morgan
> >
Received on Tue Jul 02 2002 - 17:40:55 CEST