How to join tables only with the latest record in SQL SERVER

Question

I want to list all customer with the latest phone number and most recent customer type the phone number and type of customers are changing periodically so I want the latest record only without getting old values based on the lastestupdate column

Customer:

+------------+--------------------+------------+
|latestUpdate| CustID |  AddID    |  TypeID    |
+------------+--------+-----------+-------------
| 2020-03-01 |      1 |    1      |     1     |
| 2020-04-07 |      2 |    2      |     2     |
| 2020-06-13 |      3 |    3      |     3     |
| 2020-03-29 |      4 |    4      |     4     |
| 2020-02-06 |      5 |    5      |     5     |
+------------+--------+------------+----------+

CustomerAddress:

+------------+--------+-----------+
|latestUpdate| AddID  | Mobile    |
+------------+--------+-----------+
| 2020-03-01 |      1 | 66666     |
| 2020-04-07 |      1 | 55555     |
| 2020-06-13 |      2 | 99999     |
| 2020-03-29 |      3 | 11111     |
| 2020-02-06 |      3 | 22222     |
+------------+--------+-----------+

CustomerType:

+------------+--------+-----------+
|latestUpdate| TypeId | TypeName  |
+------------+--------+-----------+
| 2020-03-01 |      1 |  First    |
| 2020-04-07 |      1 | Second    |
| 2020-06-13 |      3 | Third     |
| 2020-03-29 |      4 | Fourth    |
| 2020-02-06 |      5 | Fifth     |
+------------+--------+-----------+

When I tried to join I am always getting duplicated customerID not only the latest record

I want to Display Customer.CustID and CustomerType.TypeName and CustomerAddress.Mobile

You want what columns in the query? – Nikhil S Oct 20 '22 at 08:08 — Nikhil S, Oct 20 '22 at 08:08
Why do not use analytic function `ROW_NUMBER()`? – HRK Oct 20 '22 at 08:10 — HRK, Oct 20 '22 at 08:10

score 0 · Answer 1 · answered Oct 20 '22 at 08:12

You need to make sub-queries for most recent customer type and latest phone number like this:

SELECT *
FROM (
    SELECT latestUpdate, CustID, AddID, TypeID,
        ROW_NUMBER() OVER (PARTITION BY CustID ORDER BY latestUpdate DESC) AS RowNumber
    FROM Customer
) AS c
    INNER JOIN (
        SELECT latestUpdate, AddID, Mobile,
            ROW_NUMBER() OVER (PARTITION BY AddId ORDER BU ltestUpdate DESC) AS RowNumber
        FROM CustomerAddress
    ) AS t
        ON c.AddId = t.AddId
    INNER JOIN CustomerType ct
        ON ct.TypeId = c.TypeId
WHERE c.RowNumber = 1
    AND t.RowNumber = 1

score 0 · Answer 2 · answered Oct 20 '22 at 08:12

A simpler way than using row_number would be using cross apply together with top 1 in an ordered subquery:

select c.CustId, p.Mobile
from Customer c
  cross apply (
    select top 1 Mobile
    from CustomerAddress a
    where c.CustId = a.AddId
    order by a.latestUpdate
  ) p

score 0 · Answer 3 · answered Oct 20 '22 at 08:12

You need to use some subqueries :

SELECT *
FROM   Customer AS C
       LETF OUTER JOIN (SELECT *, ROW_NUMBER() OVER(PARTITION BY CustID ORDER BY LastestUpdate DESC) AS N
                        FROM   CustomerAddress) AS A
          ON C.CustID = A.CustID AND N = 1
       LETF OUTER JOIN (SELECT *, ROW_NUMBER() OVER(PARTITION BY CustID ORDER BY LastestUpdate DESC) AS N
                        FROM   CustomerType) AS T
          ON C.CustID = T.CustID AND N = 1

If you have had used Temporal table which is an ISO SQL Standard feature for data history of table, you will always have the lastest rows inside the main table, old rows stays into history table and can be queried with a time point or date interval restriction.

score -1 · Answer 4 · answered Oct 20 '22 at 08:12

This is it:

select * from (select *,RANK() OVER (
    PARTITION BY b.AddID    
    ORDER BY b.latestUpdate DESC,
) as rank1
from 
Customer a
left join
CustomerAddress b
on 
a.AddID=b.AddID        
left join
CustomerType c
on
v.TypeId =c.TypeId 
) where rank1=1;

score -1 · Answer 5 · answered Oct 20 '22 at 08:14

-1

You should join the tables using the "APPLY" operator. See: Link

answered Oct 20 '22 at 08:14

Denny Landrock

13
4

How to join tables only with the latest record in SQL SERVER

5 Answers5