Q:

SQL How to select max dates from results of a join

I am a bit new to SQL on the whole and I managed to stumble my way through this far but now I am a bit stuck. 
I have two tables one is an index of "Ships" to "Oil Types" just Id's.  The other table "ROB" is the amount of oil remaining on the ship. 
Each month I may or may not get a new record in the ROB table. 
What I need is each distinct oil type for a cetain ship and the latest ROB if there is one. 
The following query nets me almost what I want, except I get all ROB's for each oil type and I want only the latest. 
PARAMETERS QueryShipID Short;
SELECT Oils. OilID, Oils. ShipID, ROB.LastROB, ROB.Received, ROB.DateReceived, ROB.PortReceived, ROB.TotalUsed, ROB.CurrentROB, ROB.DateSent
FROM 
    (SELECT DISTINCT OilID, ShipID 
    FROM [Index] 
    WHERE (((ShipID)=[QueryShipID])))  
    AS Oils 

    LEFT JOIN 

    (SELECT ShipID, OilId, LastROB, Received, DateReceived, PortReceived, TotalUsed, CurrentROB, DateSent 
    FROM [Oil ROB])  
    AS ROB 

    ON (Oils. ShipID = ROB.ShipID) AND (Oils. OilID = ROB.OilID);

What I need to do is get the last DateSent for each oil type. 
I have found a few tutorials on using the Max function which seem to suggest joining the results of an aggregate query back to the orginal table.  However I can't seem to get this to work and it makes a huge and messy query. 
If anyone has some tips on the best way to do this I would be very grateful. 
Ideally after doing this I would like to add a parameter for date so you can get the last ROB before a certain date.  This should be relatively easy but I include it in case it has any bearing on the answers. 
Thanks you. 
Dave

A:

Thanks nawful
I am posting the answer myself as it is a slight modification of what nawful posted.  Also the link K'leg posted was helpful so thanks. 
This gets me exactly what I want...  
SELECT    Oils. OilID, Oils. ShipID, ROB.LastROB, ROB.Received, ROB.DateReceived,  
          ROB.PortReceived, ROB.TotalUsed, ROB.CurrentROB, ROB.DateSent 
FROM  
          ( 
           SELECT DISTINCT OilID, ShipID  
           FROM   [Index]  
           WHERE  ShipID = [QueryShipID] 
          ) AS Oils             
LEFT JOIN   
          ( 
           SELECT ShipID, OilId, LastROB, Received, DateReceived, PortReceived,  
                  TotalUsed, CurrentROB, DateSent  
           FROM   [Oil ROB]  
          )  AS ROB ON Oils. ShipID = ROB.ShipID AND Oils. OilID = ROB.OilID
WHERE     ROB.DateSent = ( 
                          SELECT MAX(DateSent)  
                          FROM   [Oil ROB]  
                          WHERE  ShipID = ROB.ShipID AND  
                                 OilID = ROB.OilID
                         );

The main modification is that I need the OilID from [Oil ROB] table to match the ROB.OilID not Oils. OilID.  Also I moved the WHERE to the outermost SELECT. 
Couldn't have done it without you though :)

