Re: Inner join ?

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

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 - 11:01:40 CEST