How to Get the Last (Latest/Newest) Record in a One-to-Many Relationship/Join

Good day everyone and welcome back to SQL Something!

Today we're looking at an issue that was presented to me today: How to get the last record in a one to many relationship.

Some digging online found this original post here, which I then had to deconstruct to understand it. To that end, I made a couple test tables and then went through the query, while making small modifications to suit my needs.

Let's go through it.


First I had two tables: 'Trans' (Fig. 1) and 'Files' (Fig. 2). 'Files' is linked to the 'Trans' table via the foreign key 'Tran_ID'.

Fig. 1: Trans table data.

Fig. 2: Files table data.

If we ran an inner join on the two we get all rows in 'Files' that match the 'Trans_ID' column in 'Trans'. We however only want the latest matching records.


To achieve that we can look at the following:

        SELECT f.Tran_ID, f.File_ID, f.Message, f.Date
        FROM Trans t
                     JOIN Files f ON
                             (t.Tran_ID = f.Tran_ID)
                     LEFT OUTER JOIN Files f2 ON
                             (t.Tran_ID = f2.Tran_ID
                             AND f.File_ID < f2.File_ID)
        WHERE f2.File_ID IS NULL;

 

What the above does first is perform a normal Inner Join on the two tables. It gets interesting when it then performs a Left Outer Join using the previous output as the left input.

The query attempts to match the Tran_IDs of the both inputs together and then all File_IDs in the left input which are less in value than the File_IDs in the right input. This means that we would not get matches in the right input for the last entries tied to a particular row in the left. This info, however, is exactly what we are trying to find.

The key to getting that info lies with the decision to use an Left Outer Join as opposed to a regular Join. As this is a Left Outer Join, this will pull all data from the left input and use NULLs as placeholders for unmatched data in the right input. We then filter all the data retrieved for data where the right input consists of NULLs, thus giving us the last row entered from the left. :-)

Fig. 3: End results!

DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)