Re: Obtaining a Column Which has MAX in Another

"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

Smartin.

My solution is:
SELECT

      accountid,
      payid
   FROM
      payments
   WHERE
      (accountid, paydate) IN (
         SELECT
               accountid,
               MAX( paydate ) as paydate
            FROM
               payments
            GROUP BY
               accountid
      )

ACCOUNTID PAYID

---------------------- ---------------------- 
100                    2
213                    3

2 rows selected

I am not certain that the in-line view works in Oracle 7.3. If not, use a temporary table to store the results of the innermost query and use another query to perform an inner join between the temporary table and PAYMENTS.

Douglas Hawthorne Received on Tue May 02 2006 - 03:04:01 CEST