Returning the Most Recent Element from a Related Entity

Problem

You want data from one entity (say Orion.Nodes) but you want to join that with another entity containing historical data but only want the property with the first (or last) timestamp. Even if the entities are connected by Navigation Properties , you can't easily get the first or last element from something connected because of the historical nature of the call.

Scenario

This article was heavily inspired by Adding NodeNotes to a SWQL query  and we'll work through a simplified version of that scenario.  The question was about pulling in Node Notes from a related entity, but only showing the most recent entry for each node.

Preparation

Source Query

Start with your original query (without the historical information) and make sure you are satisfied with the results thus far.  In our example, we'll be asking for Node information. This Source Query contains the top-level information we want to return.  In this case, the DisplayName (Caption), IP Address, Description, and Last Update dates.

SELECT [SourceQuery].DisplayName
     , [SourceQuery].IPAddress
     , [SourceQuery].Description
FROM Orion.Nodes AS [SourceQuery]

WHERE [SourceQuery].Vendor = 'Windows'

We have what we want thus far and now we need to connect it to the most recent Note.

Historical Query

Next, we'll have to turn our attention to the historical query.  For this scenario, we want the most recent Node Note (stored in the Orion.NodeNotes entity).  If we were to pull the Node Note with the MAX timestamp, we would only get the most recent 1 note instead of getting the most recent for each entry.  To begin, we'll have to start with all the fields we want in the selected entity - even including older notes for a specific Node.

SELECT [NodeNotes].NodeID
     , [NodeNotes].Note
     , [NodeNotes].Timestamp
FROM Orion.NodeNotes AS [NodeNotes]

As you can see, Node ID 12 has multiple entries.  In our scenario, we only want the most recent.

Filtering for Most Recent

To filter only for the most recent record by Node ID, we'll need to use a subquery.  This subquery simply asks for the row from the above historical results where the timestamp is the maximum timestamp and filtered by the Node ID from the parent query.

SELECT [NodeNotes].NodeID
     , [NodeNotes].Note
     , [NodeNotes].Timestamp
FROM Orion.NodeNotes AS [NodeNotes]
WHERE [NodeNotes].Timestamp = (
          -- * SUBQUERY BEGIN --> named as [NoteMostRecent]
          -- Return ONLY the most recent timestamp from the NodeNotes table
          SELECT MAX([NoteMostRecent].Timestamp) AS [MostRecentTS]
          FROM Orion.NodeNotes AS [NoteMostRecent]
          -- Where the NodeID mataches the Node ID from the parent query
          WHERE [NodeNotes].NodeID = [NoteMostRecent].NodeID
          -- * SUBQUERY END   --> named as [NoteMostRecent]
          )

Now we have the details from the historical query we want. The last thing we'll need to do is join the source and historical queries.

Connecting Source with Historical

Just like traditional SQL, we can connect these two queries together in SWQL with a JOIN operation.  From the source, we need all of the properties, and from the historical, we only need the Note.  These two queries have the common element of the NodeID (even through it's not displayed, we know it's a property in the Orion.Nodes entity.)

The overall formatting looks something like this:

SELECT [Source].[Property1]
     , [Source].[Property2]
     , [Source].[CommonProperty]
     , [Historical].[HistoricalProperty]
FROM [Source]
-- We use a LEFT here becasue we want all records from the Source Query
LEFT JOIN (
   [SubQuery]
   ) AS [Historical]
  ON [Source].[CommonProperty] = [Historical].[CommonProperty]
WHERE [Filters on Source or Historical]

For our scenario, we would get this query

SELECT [SourceQuery].[DisplayName]
     , [SourceQuery].[IPAddress]
     , [SourceQuery].[Description]
     , [Historical].[Note]
FROM Orion.Nodes AS [SourceQuery]
-- We use a LEFT here becasue we want all records from the Source Query
LEFT JOIN (
     SELECT [NodeNotes].NodeID
          , [NodeNotes].Note
          , [NodeNotes].Timestamp
     FROM Orion.NodeNotes AS [NodeNotes]
     WHERE [NodeNotes].Timestamp = (
               -- * SUBQUERY BEGIN --> named as [NoteMostRecent]
               -- Return ONLY the most recent Timestamp from the NodeNotes table
               SELECT MAX([NoteMostRecent].Timestamp) AS [MostRecentTS]
               FROM Orion.NodeNotes AS [NoteMostRecent]
               -- Where the NodeID mataches the Node ID from the parent query
               WHERE [NodeNotes].NodeID = [NoteMostRecent].NodeID
                    -- * SUBQUERY END   --> named as [NoteMostRecent]
               )
     ) AS [Historical]
     ON [SourceQuery].[NodeID] = [Historical].[NodeID]

Conclusion

There we have it - a query that pulls the most recent property from a related entity and displays it with other content.  If you want to eliminate the "NULL" values, you can always wrap the property in the IsNull function to clean it up.

SELECT [SourceQuery].[DisplayName]
     , [SourceQuery].[IPAddress]
     , [SourceQuery].[Description]
     -- Optionally remap anything null to another output
     , IsNull([Historical].[Note], '') AS [Note]
FROM Orion.Nodes AS [SourceQuery]
-- We use a LEFT here becasue we want all records from the Source Query
LEFT JOIN (
     SELECT [NodeNotes].NodeID
          , [NodeNotes].Note
          , [NodeNotes].Timestamp
     FROM Orion.NodeNotes AS [NodeNotes]
     WHERE [NodeNotes].Timestamp = (
               -- * SUBQUERY BEGIN --> named as [NoteMostRecent]
               -- Return ONLY the most recent Timestamp from the NodeNotes table
               SELECT MAX([NoteMostRecent].Timestamp) AS [MostRecentTS]
               FROM Orion.NodeNotes AS [NoteMostRecent]
               -- Where the NodeID mataches the Node ID from the parent query
               WHERE [NodeNotes].NodeID = [NoteMostRecent].NodeID
                    -- * SUBQUERY END   --> named as [NoteMostRecent]
               )
     ) AS [Historical]
     ON [SourceQuery].[NodeID] = [Historical].[NodeID]
WHERE [SourceQuery].Vendor = 'Windows'

The logic above is for the most recent, but you can change the logic from MAX to MIN to pull the "oldest" entry or play around with other logic.