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

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 Received on Tue May 02 2006 - 12:18:47 CEST