Obtaining a Column Which has MAX in Another

From: Smartin <smartin108_at_yahoo.com>
Date: Fri, 28 Apr 2006 20:02:30 -0400
Message-ID: <F5WdnSLay4eFMM_ZnZ2dneKdnZydnZ2d@giganews.com>

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

Received on Fri Apr 28 2006 - 19:02:30 CDT