Select final state of a row with versioning

Question

I have a table like this

ID  Value1  Value2  value3  Versioning
1   sport   tennis    2         1
1   NULL    NULL      4         2
1   NULL    football NULL       3
1   game    NULL     NULL       4

This is actually a Custom replicated table from one database to another. The logic is the following: The first row you replicate (versioning=1) comes with all its fields . Then every time you have any update on the original table only the changed values are replicated and not the whole table. So after 4 versionings we end up like above. What i need to do is to create a query that can read this table and return only one row that is actually the last state. Using our example table my desired outcome will be

ID Value1 Value2      Value3
1   game   football     4

Explaining the outcome for Value1 int the 1st version i have 'sport' in the 2nd and 3rd we dont have any change and in 4th version it was updated to 'game' . Respectively for the other values we have tennis -> No change -> football -> No change and for value 3 we have 2 -> 4-> No change -> No change with each -> standing for a version.

None of the value columns in the source table allow nulls, right? A replicated row with `1`, NULL, NULL, NULL, `5` would be only a bit perplexing. — HABO, Nov 25 '19 at 19:06

GarethD · Accepted Answer · 2019-11-25T16:35:58.137

You can do this by combining your versioning and your value into a single binary column, then selecting the max. The query in it's shortest form would be:

SELECT  t.ID,
        Value1 = CONVERT(VARCHAR(50), SUBSTRING(MAX(CONVERT(BINARY(4), t.Versioning) 
                    + CONVERT(VARBINARY(50), t.Value1)), 5, 50)),
        Value2 = CONVERT(VARCHAR(50), SUBSTRING(MAX(CONVERT(BINARY(4), t.Versioning) 
                    + CONVERT(VARBINARY(50), t.Value2)), 5, 50)),
        Value3 = CONVERT(INT, SUBSTRING(MAX(CONVERT(BINARY(4), t.Versioning) 
                    + CONVERT(VARBINARY(50), t.Value3)), 5, 50))
FROM    YourTable AS t
GROUP BY ID;

To explain what is going on, I will focus on just value 3, with cutdown sample data.

The first step in the process is just to combine the ordering column, and the value column into a single binary value:

SELECT  *,
        BinaryValue3 = CONVERT(BINARY(2), t.Versioning)  + CONVERT(BINARY(2), t.Value3)     
FROM    (VALUES (1, 2, 1), (1, 4, 2), (1, NULL, 3)) AS t (ID, Value3, Versioning)

Which gives:

ID      Value3  Versioning      BinaryValue3
--------------------------------------
1       2           1           0x00010002
1       4           2           0x00020004
1       NULL        3           NULL

We then take the maximum of the binary value. This relies on two things:

The fact that concatenating NULL will yield NULL, so there is only a binary value for non-null records
Since binary values will sort from left to right, the MAX function will always pick up the binary value with the highest versioning number

Then once we have our maximum binary value (0x00020004), it is just a case of extracting the right hand side, and converting it back to the original data type.

Full Working Demo

DECLARE @T TABLE 
(
    ID INT NOT NULL, 
    Value1  VARCHAR(50), 
    Value2 VARCHAR(50), 
    value3 INT, 
    Versioning INT NOT NULL,
    PRIMARY KEY (ID, Versioning)
);
INSERT @T (ID, Value1, Value2, Value3, Versioning)
VALUES
    (1, 'sport', 'tennis', 2, 1),
    (1, NULL, NULL, 4, 2),
    (1, NULL, 'football', NULL, 3),
    (1, 'game', NULL, NULL, 4);

SELECT  t.ID,
        Value1 = CONVERT(VARCHAR(50), SUBSTRING(MAX(CONVERT(BINARY(4), t.Versioning) 
                    + CONVERT(VARBINARY(50), t.Value1)), 5, 50)),
        Value2 = CONVERT(VARCHAR(50), SUBSTRING(MAX(CONVERT(BINARY(4), t.Versioning) 
                    + CONVERT(VARBINARY(50), t.Value2)), 5, 50)),
        Value3 = CONVERT(INT, SUBSTRING(MAX(CONVERT(BINARY(4), t.Versioning) 
                    + CONVERT(VARBINARY(50), t.Value3)), 5, 50))
FROM    @T AS t
GROUP BY ID;

You can also use this method with window functions to add the last non-null value to each row, so if you wanted to fill in all nulls, with the last non null value you can:

DECLARE @T TABLE 
(
    ID INT NOT NULL, 
    Value1  VARCHAR(50), 
    Value2 VARCHAR(50), 
    value3 INT, 
    Versioning INT NOT NULL,
    PRIMARY KEY (ID, Versioning)
);
INSERT @T (ID, Value1, Value2, Value3, Versioning)
VALUES
    (1, 'sport', 'tennis', 2, 1),
    (1, NULL, NULL, 4, 2),
    (1, NULL, 'football', NULL, 3),
    (1, 'game', NULL, NULL, 4);

SELECT  t.ID,
        ActualValue1 = t.Value1,
        ActualValue2 = t.Value2,
        ActualValue3 = t.Value3,
        LastNonNUllValue1 = CONVERT(VARCHAR(50), SUBSTRING(MAX(Value1Bin) OVER(PARTITION BY t.ID ORDER BY t.Versioning), 5, 50)),
        LastNonNUllValue2 = CONVERT(VARCHAR(50), SUBSTRING(MAX(Value2Bin) OVER(PARTITION BY t.ID ORDER BY t.Versioning), 5, 50)),
        LastNonNUllValue3 = CONVERT(INT, SUBSTRING(MAX(Value3Bin) OVER(PARTITION BY t.ID ORDER BY t.Versioning), 5, 50)),
        t.Versioning
FROM    @T AS t
        CROSS APPLY
        (   SELECT  Value1Bin = CONVERT(BINARY(4), t.Versioning) + CONVERT(VARBINARY(50), t.Value1),
                    Value2Bin = CONVERT(BINARY(4), t.Versioning) + CONVERT(VARBINARY(50), t.Value2),
                    Value3Bin = CONVERT(BINARY(4), t.Versioning) + CONVERT(VARBINARY(50), t.Value3)
        ) AS b
ORDER BY t.Versioning;

Which gives:

ID  ActualValue1    ActualValue2    ActualValue3    LastNonNUllValue1   LastNonNUllValue2   LastNonNUllValue3   Versioning
------------------------------------------------------------------------------------------------------------------------------
1   sport           tennis          2               sport               tennis                  2                   1
1   NULL            NULL            4               sport               tennis                  4                   2
1   NULL            football        NULL            sport               football                4                   3
1   game            NULL            NULL            game                football                4                   4

For more reading see Itzik Ben-Gan's The Last non NULL Puzzle

score 1 · Answer 2 · answered Nov 25 '19 at 15:43

This is rather tricky in SQL Server, because it does not support the ignore nulls option on window functions. You can use repetitive applys, one for each column:

select t.id, t1.value1, t2.value2, t3.value3
from (values (1)) t(id) outer apply
     (select top (1) t2.value1
      from yourtable t1
      where t1.id = t.id and t1.value1 is not null
      order by t1.versioning desc
     ) t1 outer apply 
     (select top (1) t2.value1
      from yourtable t2
      where t2.id = t.id and t2.value1 is not null
      order by t2.versioning desc
     ) t2 outer apply 
     (select top (1) t3.value1
      from yourtable t3
      where t3.id = t.id and t3.value1 is not null
      order by t3.versioning desc
     ) t3;

score 1 · Answer 3 · answered Nov 25 '19 at 16:05

With a CTE that returns the [Versioning] for the latest non null value of each of the columns [ValueX] and then joins to the table:

with cte as (
  select [ID],
    max(case when [Value1] is not null then [Versioning] end) v1,
    max(case when [Value2] is not null then [Versioning] end) v2,
    max(case when [Value3] is not null then [Versioning] end) v3
  from tablename
  group by [ID]
)
select c.[ID], t1.[Value1], t2.[Value2], t3.[Value3]
from cte c
inner join tablename t1 on t1.[ID] = c.[ID] and t1.[Versioning] = c.v1
inner join tablename t2 on t2.[ID] = c.[ID] and t2.[Versioning] = c.v2
inner join tablename t3 on t3.[ID] = c.[ID] and t3.[Versioning] = c.v3

See the demo.
Results:

> ID | Value1 | Value2   | Value3
> -: | :----- | :------- | :-----
>  1 | game   | football | 4

John Cappelletti · Answer 4 · 2019-11-25T16:59:19.417

Yet one more option.

Here we unpivot your data, and then pivot

Example

Select *
 From  (
        Select Top 1 with ties 
               A.ID
              ,B.*
         From  YourTable A
         Cross Apply ( values ('Value1',Value1)
                             ,('Value2',Value2)
                             ,('Value3',convert(varchar(50),Value3))
                     ) B(Item,Value)
          Where Value is not null
          Order By row_number() over (partition by id,item order by versioning desc)
       ) pvt
 Pivot (max(value) for item in ([Value1],[Value2],[Value3]) ) pvt

Returns

ID  Value1  Value2      Value3
1   game    football    4

score 1 · Answer 5 · answered Nov 25 '19 at 20:09

Assuming your table is 'tablename' and following code gives latest value and can be expandable to any other columns

select 
(SELECT TOP 1 Value1 FROM tablename WHERE Value1 IS NOT NULL ORDER BY Versioning desc) Value1,
(SELECT TOP 1 Value2 FROM tablename WHERE Value2 IS NOT NULL ORDER BY Versioning desc) Value2,
(SELECT TOP 1 Value3 FROM tablename WHERE Value3 IS NOT NULL ORDER BY Versioning desc) Value3

Select final state of a row with versioning

5 Answers5