Query to write latest changed customer id

Question

Тhe customer id get updated regularly. There is a table that records this change.

Customer id, Customer’s Previous id, Customer’s Successor id
100,95,102
101,96,105
102,100,104
103,99,106
107,88,111

We need from this data for each customer id, that latest changed customer id. A sample of that from above data would be:

Customer id, Latest Customer id
100,104
101,105
102,104
103,106
107,111

Answer 1

You can also use LEFT JOIN if ID is updated only once

SELECT A.CID,COALESCE(B.SCID,A.SCID) AS LCID
FROM #TABLEA A LEFT JOIN #TABLEA B ON A.CID=B.PCID

CHECK DEMO HERE

Output

+---------------+-------------------+
| Customer ID   | Latest Customer id|
+---------------+-------------------+
| 100           | 104               |
| 101           | 105               |
| 102           | 104               |
| 103           | 106               |
| 107           | 111               |
+---------------+-------------------+

Note: Here I have refereed Customer ID as CID, Previous Customer ID as PCID & Successor Customer ID as SCID