thread.sql

sqlite> WITH RECURSIVE
up AS (
  SELECT reference, 0 AS n
  FROM mails
  WHERE msgid = 'ed7f0232-1e16-8085-f9e2-eb0d77c87184@gmail.com'
  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 |george+freebsd@m5p.com|bdd3bb04-2b0f-34bb-18c3-0abec1b11328@m5p.com|Shy scroll bars|
 2022-05-17 16:10:54|Guido Falsi |mad@madpilot.net|3433c3f3-86da-7f0e-8a84-2ad80946302d@madpilot.net|Re: Shy scroll bars|bdd3bb04-2b0f-34bb-18c3-0abec1b11328@m5p.com
  2022-05-17 17:59:10|George Mitchell |george+freebsd@m5p.com|b5e1ff7c-bc7b-c033-c6f1-55d3fce106ab@m5p.com|Re: Shy scroll bars|3433c3f3-86da-7f0e-8a84-2ad80946302d@madpilot.net
  2022-05-22 11:18:34|Graham Perrin |grahamperrin@gmail.com|ed7f0232-1e16-8085-f9e2-eb0d77c87184@gmail.com|Handsome scroll bars and devilish numbers|3433c3f3-86da-7f0e-8a84-2ad80946302d@madpilot.net
   2022-05-22 12:24:36|Guido Falsi |mad@madpilot.net|b81ef5d1-49f5-3776-906b-e74910586650@madpilot.net|Re: Handsome scroll bars and devilish numbers|ed7f0232-1e16-8085-f9e2-eb0d77c87184@gmail.com
    2022-05-22 15:00:25|Philipp Ost |pj@smo.de|b6b96e56-8509-3eef-e6b0-210e577f9d3f@smo.de|Re: Handsome scroll bars and devilish numbers|b81ef5d1-49f5-3776-906b-e74910586650@madpilot.net
    2022-05-23 04:00:47|Chris |bsd-lists@bsdforge.com|9930884f80c47409a68840562a498471@bsdforge.com|Re: Handsome scroll bars and devilish numbers|b81ef5d1-49f5-3776-906b-e74910586650@madpilot.net
     2022-05-23 07:19:50|Guido Falsi |mad@madpilot.net|5ffa9060-d175-1758-adff-f30ade25b313@madpilot.net|Re: Handsome scroll bars and devilish numbers|9930884f80c47409a68840562a498471@bsdforge.com
 2022-05-17 16:14:17|Chris |bsd-lists@bsdforge.com|bc5a74c4e390e1a2d807e9b3e3331959@bsdforge.com|Re: Shy scroll bars|bdd3bb04-2b0f-34bb-18c3-0abec1b11328@m5p.com
  2022-05-17 16:21:52|Michael Schuster |michaelsprivate@gmail.com|CADqw_gLeGGDMW6cpmz-dDAkCuYu0vTbpjVn3WXEDxpjVTcF18Q@mail.gmail.com|Re: Shy scroll bars|bc5a74c4e390e1a2d807e9b3e3331959@bsdforge.com
 2022-05-17 21:51:23||henrichhartzer@tuta.io|N2J-GIx--7-2@tuta.io|Re: Shy scroll bars|bdd3bb04-2b0f-34bb-18c3-0abec1b11328@m5p.com
  2022-05-17 22:22:37|George Mitchell |george+freebsd@m5p.com|cb547bfe-200d-b4f9-0157-70aa82be4258@m5p.com|Re: Shy scroll bars|N2J-GIx--7-2@tuta.io
   2022-05-18 06:52:46|Guido Falsi |mad@madpilot.net|192d767e-a644-405e-b2b4-bb31a71a4af1@madpilot.net|Re: Shy scroll bars|cb547bfe-200d-b4f9-0157-70aa82be4258@m5p.com
    2022-05-18 18:29:50|George Mitchell |george+freebsd@m5p.com|37d165ec-cc47-2940-3c05-159640849f1f@m5p.com|Re: Shy scroll bars|192d767e-a644-405e-b2b4-bb31a71a4af1@madpilot.net