Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Super Newbie question part two

Re: Super Newbie question part two

From: Jonathan Gennick <gennick_at_worldnet.att.net>
Date: 1997/09/11
Message-ID: <5v7dhc$qst@bgtnsc03.worldnet.att.net>#1/1

On Wed, 10 Sep 1997 08:43:17 -0700, Len Wolfenstein <lawolfe_at_msg.pacbell.com> wrote:

>Hi. Thanks for all the responses to my first question :). If you have
>the time, here is another one:
>
>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.
>
>Is this a scenario where one would use nested SELECTS or could this be
>accomplished with GROUP BY?

If you want JUST the date, you can probably use "group by". For example:

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

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);

Hope this helps.

Jonathan Gennick

>
>Thanks in advance,
>Len
>



Jonathan Gennick
gennick_at_worldnet.att.net
"Brighten the Corner Where you Are" Received on Thu Sep 11 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US