Join a table to itself, each row needs to join to the ordered last record I have a logging table which for whatever reason logs the column and table that changed, the date it changed and also the value it was changed FROM (the old value). I want to join these records together so that I can see what the record changed TO. (The very newest value is obviously not going to appear as that is never logged). Each record has a unique identifier also.

so I have

| UID | historyFor | DateUpdated | Table      | Column    | oldValue |
| 1   | uid=123    | 01/01/2019  | Person     | firstname | Joe      |
| 2   | uid=123    | 02/01/2019  | Person     | firstname | Bill     |
| 3   | uid=123    | 03/01/2019  | Person     | firstname | Elliot   |


I want to add a column so that it would be 

| UID | historyFor | DateUpdated | Table      | Column    | oldValue | nextRecord |
| 1   | uid=123    | 01/01/2019  | Person     | firstname | Joe      | Bill |        
| 2   | uid=123    | 02/01/2019  | Person     | firstname | Bill     | Elliot     |
| 3   | uid=123    | 03/01/2019  | Person     | firstname | Elliot   | &lt;blank&gt;    |


nextValue should contain the oldValue from the next record when sorted ascending on DateUpdated. Note there are all different records in here so it has to join on historyFor and find the next most recent oldValue and put it in nextRecord. Is there a way to do this? I'm thinking a top 1 greater than DateUpdated will come into play but I'm just not sure how this is going to work.
You can use LEAD() function to get the next value:

Quoted from docs:


  Accesses data from a subsequent row in the same result set without the use of a self-join starting with SQL Server 2012 (11.x). LEAD provides access to a row at a given physical offset that follows the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a following row.


SELECT
  UID,
  historyFor,
  DateUpdated,
  [Table],
  [Column],
  oldValue,
  LEAD(oldValue) OVER (PARTITION BY [Table], [Column]
                       ORDER BY DateUpdated) nextValue
FROM
  t;




UID | historyFor | DateUpdated         | Table  | Column    | oldValue | nextValue
--: | :--------- | :------------------ | :----- | :-------- | :------- | :--------
  1 | uid=123    | 01/01/2019 00:00:00 | Person | firstname | Joe      | Bill     
  2 | uid=123    | 01/02/2019 00:00:00 | Person | firstname | Bill     | Elliot   
  3 | uid=123    | 01/03/2019 00:00:00 | Person | firstname | Elliot   | null     



db&lt;>fiddle here