Re: Inner join ?

Daniel,

thank you for your assistance.

For someone obviously mentally retarded, I guess I've done quite well so far...

Jurriaan

"Daniel Morgan" <dmorgan_at_exesolutions.com> schreef in bericht news:3D21C608.E56B9D95_at_exesolutions.com...
> Jurriaan Themmen wrote:
>
> > 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
> > >
>
> I'm at a loss as to how you can be doing anything in Oracle and not know
 Oracle
> data types.
>
> But I will take you at your word that you are not an employee and provide
 you a
> more thorough answer.
>
> SELECT fields
> FROM table1 a
> WHERE EXISTS (
> SELECT MAX(update)
> FROM table1 b
> WHERE a.material = b.material
> AND a.item = b.item);
>
> With respect to speed:
> Have you run DBMS_STATS so the optimizer knows what it is doing?
> Have you run EXPLAIN PLAN on your DML?
> Have you built the proper indexes?
>
> I suspect not because I am still at a loss to figure out how you could
 have been
> hired to do a job for which you are so obviously unqualified. I've no
 doubt
> thousands of people would like to know the secret of how to get hired for
 an
> Oracle job when you are qualified and here you somehow slipped under the
 radar
> screen. Amazing.
>
> Daniel Morgan
>
Received on Wed Jul 03 2002 - 07:35:17 CEST