Sql query to return a chain of events

Question

I have a table that tracks changes. One column is called beforename and the other aftername.

Some sample data might be:

BeforeName   AfterName
a            b
b            c
c            d

I am trying to write a query that will self reference itself in such a way as to return the changes ie:

a->b->c->d (the arrows are just for notation here)

Is this possible to do in SQL?

My database is Sqlite

TIA

Answer 1

In fact, you can using recursive CTEs. Assuming no cycles:

with cte as (
      select beforename, aftername as lastone, beforename || '->' || aftername as path, 1 as lev
      from t
      where not exists (select 1 from t t2 where t2.aftername = t.beforename)
      union all
      select cte.beforename, t.aftername as lastone, path || '->' || aftername, lev + 1
      from cte join
           t
           on cte.lastone = t.beforename
     )
select *
from cte
where lev = (select max(cte2.lev) from cte cte2 where cte2.beforename = ce.beforename);

Or, if your version of SQLite supports window functions:

select *
from (select cte.*, row_number() over (partition by beforename order by lev desc) as seqnum
      from cte
     ) x
where seqnum = 1;