DOCUMENT #720
     =======================================================================
     SQL TIP:  SELECT THE LAST RECORD FOR EACH GROUP
     =======================================================================
     PRODUCT:  R:BASE                   VERSION :  3.1 or Higher
     =======================================================================
     CATALOG:  SQL                      AREA    :  SELECT
     =======================================================================
 
 
     A frequent request from customers is to be able to select the last 
     record for each group. Either an autonumber column or a DATE column 
     is needed in the table. The data is selected by assuming that the last 
     record for a group has the highest number or the latest date of all 
     the records in that group. For example, to select the last transaction 
     for each customer from the Transmaster table in the CONCOMP database:
 
       SELECT custid,transid,transdate FROM transmaster T1 WHERE +
       transdate = (SELECT MAX(transdate) FROM transmaster T2 +
       WHERE T2.custid=T1.custid)
 
     The sub-SELECT in the WHERE clause finds the maximum transaction date 
     for a particular customer. Then the data is selected from the 
     Transmaster for the row that has that date value.