Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Super Newbie question part two
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
>