Re: Obtaining a Column Which has MAX in Another

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