SQL left join with latest record

user_1041627 0
Viewed 118

I want to left join a table with the latest record only. I have Customer1 table:

+--------+----------+
| CustID | CustName |
+--------+----------+
|      1 | ABC123   |
|      2 | 456XYZ   |
|      3 | 5PQR3    |
|      4 | 789XYZ   |
|      5 | 789A     |
+--------+----------+

SalesInvoice table:

+------------+--------+-----------+
|  InvDate   | CustID | InvNumber |
+------------+--------+-----------+
| 2020-03-01 |      1 | IV236     |
| 2020-04-07 |      1 | IV644     |
| 2020-06-13 |      2 | IV869     |
| 2020-03-29 |      3 | IV436     |
| 2020-02-06 |      3 | IV126     |
+------------+--------+-----------+

And I want this required output:

+--------+------------+-----------+
| CustID |  InvDate   | InvNumber |
+--------+------------+-----------+
|      1 | 2020-04-07 | IV644     |
|      2 | 2020-06-13 | IV869     |
|      3 | 2020-03-29 | IV436     |
|      4 |            |           |
|      5 |            |           |
+--------+------------+-----------+

For quick and easy, below is the sample code.

    drop table if exists #Customer1
    create table #Customer1(CustID int, CustName varchar (100))
    insert into #Customer1 values
    (1,'ABC123'),
    (2,'456XYZ'),
    (3,'5PQR3'),
    (4,'789XYZ'),
    (5,'789A')
    
    
    drop table if exists #SalesInvoice
    create table #SalesInvoice(InvDate DATE, CustID INT, InvNumber varchar (100))
    insert into #SalesInvoice values
    ('2020-03-01',1,'IV236'),
    ('2020-04-07',1,'IV644'),
    ('2020-06-13',2,'IV869'), 
    ('2020-03-29',3,'IV436'),
    ('2020-02-06',3,'IV126')
tsql sql-server sql
3 Answers

I like using TOP 1 WITH TIES in this case:

SELECT TOP 1 WITH TIES c.CustID, i.InvDate, i.InvNumber
FROM #Customer1 c
LEFT JOIN #Invoices i ON c.CustID = i.CustID
ORDER BY ROW_NUMBER() OVER (PARTITION BY c.CustID ORDER BY i.InvDate DESC);

screen capture from demo link below

Demo

The top 1 trick here is to order by row number, assigning a sequence to each customer, with the sequence descending by invoice date. Then, this approach retains just the most recent invoice record for each customer.

user_18632290

I recommend outer apply:

select c.*, i.*
from #c c outer apply
     (select top (1) i.*
      from #invoices i
      where i.custId = c.custId
      order by i.invDate desc
     ) i;

outer apply implements a special type of join called a "lateral join". This is a very powerful construct. But when learning about them, you can think of a lateral join as a correlated subquery that can return more than one column and more than one row.

user_11440350

You can try ROW_NUMBER window function instead of lateral joins with this simple self-explaining T-SQL

SELECT      c.CustID
            , d.InvDate
            , d.InvNumber
FROM        #C c
LEFT JOIN   (
            SELECT  *
                    , ROW_NUMBER() OVER (PARTITION BY CustID ORDER BY InvDate DESC) AS RowNo
            FROM    #D
            ) d
ON          c.CustID = d.CustID
            AND d.RowNo = 1

Basically ROW_NUMBER is used to filter the "last" invoice in one table scan, instead of performing SELECT TOP 1 ... ORDER BY in the correlated query which has to be executed multiple times -- as much as the number of customers.

user_406910
Related