Re: Inner join ?

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 Tue Jul 02 2002 - 17:26:36 CEST