SQL Server query to get oldest and newest value-CodePudding

I have a table with ChangeDate, OldValue, NewValue like shown below:

changeOrder	oldValue	newValue
0	ID1	ID2
1	ID2	ID3
2	ID6	ID7
3	ID3	ID4
4	ID7	ID8

The ID's in reality are of type STRING, is it possible to get the first and last changes, for example the below result ?

Oldest	Newest
ID1	ID4
ID6	ID8

Thanks

CodePudding user response：

WITH RECURSIVE chains AS (
  SELECT 1 pos, GENERATE_UUID() AS part, oldValue, newValue
    FROM sample JOIN (
      SELECT v AS oldValue FROM sample, UNNEST([oldValue, newValue]) v
       GROUP BY 1 HAVING COUNT(v) = 1
    ) USING (oldValue)
   UNION ALL
  SELECT pos   1, part, s.oldValue, s.newValue 
    FROM chains c JOIN sample s ON c.newValue = s.oldValue
)
SELECT DISTINCT
       FIRST_VALUE(oldValue) OVER w AS Oldest,
       LAST_VALUE(newValue) OVER w AS Newest
   FROM chains
 WINDOW w AS (PARTITION BY part ORDER BY pos ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);

CodePudding user response：

Consider also below approach

with recursive iterations as (
  select 1 pos, oldValue, newValue from your_table
  where not oldValue in (select newValue from your_table)
  union all
  select pos   1, i.oldValue, t.newValue from iterations i
  join your_table t on i.newValue = t.oldValue
) 
select oldValue as Oldest, newValue as Newest 
from iterations
qualify 1 = row_number() over(partition by oldValue order by pos desc)

if applied to sample data in your question - output is

CodePudding user response：

I'm also trying to understand the expected results in question. It doesn't make sense?