Join record with most recent record on second table

Question

I have 2 tables

Delivery
--------
deliveryid int (PK)

description long varchar

DeliveryHistory
---------------
historyid int

delievryid int

statusid int

recordtime timestamp

WHat I am trying to do is a left outer join to bring back all records from table Delivery with only the most recent entry in DeliveryHistory for each delivery. However if there are no entries in the DeliveryHistory for the delivery I would like a null value

I have done this:

select d.deliveryid,d.description, h.statusid from delivery d
left outer join  Deliveryhistory h on d.deliveryid = h.deliveryid
where  h.recordtime =
       ( SELECT MAX(recordtime)
           FROM Deliveryhistory
          WHERE deliveryid = d.deliveryid)

But it only returns the rows that have an entry in DeliveryHistory.

Try h.recordTime is null OR h.RecordTime = (...) – YetAnotherUser Aug 01 '12 at 17:29 — YetAnotherUser, Aug 01 '12 at 17:29

score 2 · Accepted Answer · answered Aug 01 '12 at 17:31

2

Your where clause is resulting in all null values being excluded. Try

where  h.RecordTime is null OR
       h.recordtime =
       ( SELECT MAX(recordtime)
           FROM Deliveryhistory
          WHERE deliveryid = d.deliveryid)

answered Aug 01 '12 at 17:31

YetAnotherUser

9,156
3
39
53

score 1 · Answer 2 · answered Aug 01 '12 at 17:31

1

select d.deliveryid,d.description, h.statusid from delivery d
left outer join  Deliveryhistory h on d.deliveryid = h.deliveryid
where  (h.recordtime =
   ( SELECT MAX(recordtime)
       FROM Deliveryhistory
      WHERE deliveryid = d.deliveryid)
  or h.deliveryid = null)

answered Aug 01 '12 at 17:31

Paul Michaels

16,185
43
146
269

This doesn't work. Only deliveries with an entry in DeliveryHistory are returned. – user1247395 Aug 01 '12 at 17:46

score 0 · Answer 3 · answered Aug 01 '12 at 17:35

The existing answers are all it takes but if you'd like to do this without using a WHERE clause you can use following construct.

SELECT  d.deliveryid
        ,d.description
        , dh.statusid
FROM    Delivery d 
        LEFT OUTER JOIN (
          SELECT deliveryid, MAX(recordtime) AS recordtime
          FROM   DeliveryHistory
          GROUP BY
                 deliveryid
        ) dhm ON dhm.deliveryid = d.deliveryid                 
        LEFT OUTER JOIN DeliveryHistory dh ON dh.deliveryid = dhm.deliveryid 
                                              AND dh.recordtime = dhm.recordtime

score 0 · Answer 4 · answered Aug 01 '12 at 17:46

CTE to yield the maxrow (IFF the implementation supports CTEs ;-) plus simple left join with the CTE.

WITH last AS (
        SELECT * FROM Deliveryhistory dh
        WHERE NOT EXISTS (
                SELECT * 
                FROM Deliveryhistory nx
                WHERE nx.deliveryid = dh.deliveryid
                AND nx.recordtime > dh.recordtime -- no one is bigger: dh must be the max
                )
        )
SELECT d.deliveryid, d.description, l.statusid 
FROM delivery d
LEFT JOIN last l ON d.deliveryid = l.deliveryid
        ;

Join record with most recent record on second table

4 Answers4

Linked