Re: Obtaining a Column Which has MAX in Another

Mark C. Stock wrote:
> "Smartin" <smartin108_at_yahoo.com> wrote in message
> news:44568A84.7030405_at_yahoo.com...
> : Smartin wrote:
> : > Good Day All,
> : >
> : > I am using Oracle 7.3. Using the following table definition and sample
> : > data, how can I obtain the PayID which has the maximum PayDate for each
> : > AccountID?
> : >
> : > table Payments
> : > ==============
> : > PayID (key)
> : > AccountID (number)
> : > PayDate (date)
> : >
> : > Payments
> : > PayID AccountID PayDate
> : > ================================
> : > 1 100 2/7/2005
> : > 2 100 5/16/2005
> : > 3 213 10/12/2006
> : > 4 100 2/17/2005
> : > 5 213 9/22/2005
> : >
> : > So I'm expecting to retrieve PayId's 2 and 3.
> : >
> : > For what it's worth I was born and raised on Access and have worked out
> : > the solution using Access SQL. However it employs the LAST() function,
> : > which is not available to me in O7.3.
> : >
> : > In theory I could use a linked table in Access to do this, but in my
> : > real life application I have found this to be entirely to slow.
> : >
> : > Thanks in advance.
> :
> : I see this is quite the stumper... not because no one has replied to me,
> : but because I see the question has been posed several times on Usenet
> : over the years and no one (that I have seen so far) has received a
> : solution.
> :
> : I should revise my question to say I would like to know if there is a
> : solution in ANSI SQL. Surely someone has done this?
> :
> : Anyway, thanks for your time.
> :
> : --
> : Smartin
>
> Douglas Hawthorne's answer is the one that has been provided for the last 20
> years or so -- this is covered in most Intro to SQL classes, and you've
> almost described the answer in your description of the problem.
>
> Perhaps your Access SQL background has not included much use of sub-queries
> (what Douglas referred to as an in-line view), so the problem may have
> seemed tougher than it actually is. But if you think through your
> description, and build your SQL piece by piece, it's quite easy to come to
> the solution:
>
> : > how can I obtain the PayID which has the maximum PayDate for each
> : > AccountID?
>
> what's the first thing you need to determine? maximum PAYDATE, for each
> ACCOUNTID -- requires a simple GROUP BY
>
> once you have written the SELECT statement to return your pairs of ACCOUNTID
> and PAYDATE values, use it in a straight-forward multi-value subquery to
> determine which PAYID records have that set of values, as per Douglas'
> solution (which, AFAIR, worked in version 6 of Oracle, perhaps even versions
> 5 and 4).
>
> ++ mcs
>
>
>
>
>

The /multi-value/ part of this was eluding me. I see some remedial SQL training is in my future.

Thanks much to both of you. I'll give it a go!

-- 
Smartin