Re: Super Newbie question part two

Jonathan Gennick wrote:
>
> On Wed, 10 Sep 1997 08:43:17 -0700, Len Wolfenstein
> <lawolfe_at_msg.pacbell.com> wrote:
> >1) I have two tables that stand in a one to many relationship on field
> >"id" (unique row tblA.id may have many corresponding rows on tblB.id)
> >2) I would like to select all rows from tblA regardless of whether there
> >is a matching row in tblB (c.f. my original outer join question) and
> >for each matched row in tblA, the single row in tblB with the maximum
> >value for tblB.date.
 

> However, once you group by something, then ALL other values
> need to be aggregate values such as SUM, MAX, MIN, etc... If
> you want other values from tblB then you would have to
> somehow specify the specific row in which you are
> interested, for example:
>
> select tblA.id, max(tblB.date)
> from tblA,tblB
> where tblA.id = tblB.id
> and tblB.date = (select max(date)
> from tblB
> where tblB.id=tblA.id);

Hi Jonathan

the correct sql statement for you last example is:

    select tblA.id, tblB.<any_other_column> <-- not max(tblB.date)

      from tblA,tblB
     where tblA.id = tblB.id
       and tblB.date = (select max(date) 
                          from tblB 
                         where tblB.id=tblA.id);

otherwise you need a group by clause again as in your first example.

But, it could happen that you do not get only one single row from tblB but more. There could be more than one record with a value for tblB.date equal to the maximum value for tblB.date.

This could easily happen if the tblB.date is truncated to contain only the date information without the time. By default Oracle stores not only the date but also the time component in a date field.

Hope this helps.

Peter Ellerau Received on Fri Sep 12 1997 - 00:00:00 CDT