Performing a JOIN with the results of a WITH clause

Question

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.

GMB · Accepted Answer · 2020-05-11T19:03:37.283

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

Yes, all 3 are exactly what I need to keep going on this! Thanks so much!!!!! — wetin, May 12 '20 at 00:20

score 1 · Answer 2 · answered May 11 '20 at 19:04

Actually, I would suggest a different approach -- a lateral join:

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) NodeID, DateTime, 
        ROW_NUMBER() OVER (PARTITION BY NodeID ORDER BY DateTime DESC) AS rn
      FROM CPULoad c
      WHERE c.NodeId = p.NetObjectID
      ORDER BY DateTime DESC
     ) c;

Lateral joins are very powerful and worth learning about. They are also often a bit faster than the ROW_NUMBER() approach.

Performing a JOIN with the results of a WITH clause

2 Answers2