Performing a JOIN with the results of a WITH clause

I have one query that uses one join already, call it query1. I would like to join query1 to the results of a WITH clause. I don’t know how to merge the two data sets.

Query 1:

SELECT 
    p.NetObjectID 
    , n.Caption, n.ObjectSubType
FROM Pollers p
LEFT JOIN NodesData n ON n.NodeID = p.NetObjectID

The next query is a WITH clause. I don’t know how to obtain the results without a WITH because I need to do what is outlined in this post.

Query 2:

WITH ranked_DateStamp AS 
(
    SELECT c.NodeID, c.DateTime
        , ROW_NUMBER() OVER (PARTITION BY NodeID ORDER BY DateTime DESC) AS rn
    FROM CPULoad AS c
)
SELECT *
FROM ranked_DateStamp
WHERE rn = 1;

I thought I could just JOIN ranked_DateStamp ON ranked_DateStamp.NodeID = p.NodeID but it won’t allow it.

Answer

You don’t really need a with clause here, you can use a subquery. I would just phrase this as:

SELECT 
    p.NetObjectID, 
    n.Caption, 
    n.ObjectSubType,
    c.DateTime
FROM Pollers p
LEFT JOIN NodesData n ON n.NodeID = p.NetObjectID
LEFT JOIN (
    SELECT 
        NodeID, 
        DateTime, 
        ROW_NUMBER() OVER (PARTITION BY NodeID ORDER BY DateTime DESC) AS rn
    FROM CPULoad AS c
) c ON c.rn = 1 and c.NodeID = p.NetObjectID

However, if you were to use a common table expression, that would look like:

WITH ranked_DateStamp AS (
    SELECT 
        NodeID, 
        DateTime, 
        ROW_NUMBER() OVER (PARTITION BY NodeID ORDER BY DateTime DESC) AS rn
    FROM CPULoad AS c
)
SELECT 
    p.NetObjectID, 
    n.Caption, 
    n.ObjectSubType,
    c.DateTime
FROM Pollers p
LEFT JOIN NodesData n ON n.NodeID = p.NetObjectID
LEFT JOIN ranked_DateStamp c ON c.rn = 1 and c.NodeID = p.NetObjectID

Actually, a lateral join might perform equally well, or better:

SELECT 
    p.NetObjectID, 
    n.Caption, 
    n.ObjectSubType,
    c.DateTime
FROM Pollers p
LEFT JOIN NodesData n ON n.NodeID = p.NetObjectID
OUTER APPLY (SELECT TOP (1) * FROM CPULOad c WHERE c.NodeID = p.NetObjectID ORDER BY DateTime DESC) c