Mark C. Stock wrote:
Quote:
"Smartin" <smartin108@yahoo.comwrote in message
news:44568A84.7030405@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!
