Obtaining a Column Which has MAX in Another

[Smartin]

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.
--
Smartin

[Smartin]

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]

"Smartin" <sm********@yahoo.com> wrote in message
news:44**************@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

[Mark C. Stock]

"Smartin" <sm********@yahoo.com> wrote in message
news:44**************@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

[Smartin]

Mark C. Stock wrote:

"Smartin" <sm********@yahoo.com> wrote in message
news:44**************@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!

--
Smartin