Re: Obtaining a Column Which has MAX in Another
Date: Tue, 02 May 2006 01:04:01 GMT
Message-ID: <5ey5g.20560$vy1.13484_at_news-server.bigpond.net.au>
"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