From: mcstockX@Xenquery   
      
   "Smartin"  wrote 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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)