thread.sql

sqlite> WITH RECURSIVE
up AS (
  SELECT reference, 0 AS n
  FROM mails
  WHERE msgid = '[email protected]'
  UNION ALL
  SELECT m.reference, n + 1
  FROM mails m JOIN up ON m.msgid = up.reference AND m.reference IS NOT NULL
),
down AS (
  SELECT (SELECT up.reference AS msgid FROM up ORDER BY up.n DESC LIMIT 1) AS msgid, 0 AS n, '/' AS idx
  UNION ALL
  SELECT m.msgid, n + 1, down.idx || m.idx || '/'
  FROM mails m JOIN down ON m.reference = down.msgid
)
SELECT format('%' || CAST(19 + down.n AS TEXT) || 's', m.date), m.name, m.email, m.msgid, m.subject, m.reference
FROM mails m JOIN down ON m.msgid = down.msgid
ORDER BY down.idx;
2022-05-17 15:27:18|George Mitchell |[email protected]|[email protected]|Shy scroll bars|
 2022-05-17 16:10:54|Guido Falsi |[email protected]|[email protected]|Re: Shy scroll bars|[email protected]
  2022-05-17 17:59:10|George Mitchell |[email protected]|[email protected]|Re: Shy scroll bars|[email protected]
  2022-05-22 11:18:34|Graham Perrin |[email protected]|[email protected]|Handsome scroll bars and devilish numbers|[email protected]
   2022-05-22 12:24:36|Guido Falsi |[email protected]|[email protected]|Re: Handsome scroll bars and devilish numbers|[email protected]
    2022-05-22 15:00:25|Philipp Ost |[email protected]|[email protected]|Re: Handsome scroll bars and devilish numbers|[email protected]
    2022-05-23 04:00:47|Chris |[email protected]|[email protected]|Re: Handsome scroll bars and devilish numbers|[email protected]
     2022-05-23 07:19:50|Guido Falsi |[email protected]|[email protected]|Re: Handsome scroll bars and devilish numbers|[email protected]
 2022-05-17 16:14:17|Chris |[email protected]|[email protected]|Re: Shy scroll bars|[email protected]
  2022-05-17 16:21:52|Michael Schuster |[email protected]|CADqw_gLeGGDMW6cpmz-dDAkCuYu0vTbpjVn3WXEDxpjVTcF18Q@mail.gmail.com|Re: Shy scroll bars|[email protected]
 2022-05-17 21:51:23||[email protected]|[email protected]|Re: Shy scroll bars|[email protected]
  2022-05-17 22:22:37|George Mitchell |[email protected]|[email protected]|Re: Shy scroll bars|[email protected]
   2022-05-18 06:52:46|Guido Falsi |[email protected]|[email protected]|Re: Shy scroll bars|[email protected]
    2022-05-18 18:29:50|George Mitchell |[email protected]|[email protected]|Re: Shy scroll bars|[email protected]