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_at_giganews.com>
Good Day All,
PayID (key)
AccountID (number)
PayDate (date)
Date: Fri, 28 Apr 2006 20:02:30 -0400
Message-ID: <F5WdnSLay4eFMM_ZnZ2dneKdnZydnZ2d_at_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.
-- SmartinReceived on Sat Apr 29 2006 - 02:02:30 CEST