SQL How to select max dates from results of a join

Question

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

Technically the type lives in another table that is related by OilID to the Index and ROB tables. But it isn't really important yet... I can get to that later. — Dave Williams, May 09 '12 at 07:19

score 0 · Answer 1 · edited May 23 '17 at 12:11

0

I think I asked almost this exact same question recently try this SQL Select newest records that have distinct Name column

edited May 23 '17 at 12:11

Community

1
1

answered May 08 '12 at 17:01

General Grey

3,598
2
25
32

score 0 · Answer 2 · answered May 08 '12 at 17:18

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] 
           WHERE  DateSent = (
                              SELECT MAX(DateSent) 
                              FROM   [Oil ROB] 
                              WHERE  Oils.ShipID = ROB.ShipID AND 
                                     Oils.OilID = ROB.OilID
                             )
          )  AS ROB ON Oils.ShipID = ROB.ShipID AND Oils.OilID = ROB.OilID;

Thanks I never thought to do it like that... although it can't get Oils.ShipID or OilIR or ROB.ShipID or OilID from inside the WHERE. — Dave Williams, May 09 '12 at 07:23
If I move the WHERE outside I get only the records with the latest date not the latest date for each record. I will p[lay some more :) — Dave Williams, May 09 '12 at 07:49

score 0 · Accepted Answer · edited May 09 '12 at 12:12

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 :)

Yes thats the right way to do: I did not test or give much thought to that part when writing that answer — nawfal, May 09 '12 at 08:43

SQL How to select max dates from results of a join

3 Answers3