-- Collection of code snippets by Arne Vajhøj 
-- (likely posted to comp.os.vms/INFO-VAX, INFO-TPU, MACRO32, eksperten.dk, newz.dk, LinkedIn or other place sometime between 1990 and now) 
CREATE TABLE tabel1 (
    id INTEGER NOT NULL,
    subid INTEGER,
    navn VARCHAR(25),
    dato DATE,
    PRIMARY KEY(id)
);
INSERT INTO tabel1 VALUES(1,NULL,'Top niveau 1','2022-05-01');
INSERT INTO tabel1 VALUES(2,NULL,'Top niveau 2','2022-05-14');
INSERT INTO tabel1 VALUES(3,1,'Sub niveau til ID 1','2022-05-05');
INSERT INTO tabel1 VALUES(4,1,'Og sub niveau til ID 1','2022-05-04');
INSERT INTO tabel1 VALUES(5,2,'Sub niveau til ID 2','2022-05-15');

SELECT * FROM tabel1;

SELECT t1a.id,t1a.navn,t1a.dato
FROM tabel1 t1a LEFT JOIN tabel1 t1b ON t1a.subid = t1b.id -- t1a = row, t1b = parent row
ORDER BY COALESCE(t1b.dato,t1a.dato), -- order by parent row to order parent rows 
         COALESCE(t1b.id,t1a.id), -- order by parent row id to split multiple paraent rows with same dato
         t1a.dato; -- order by row dato to order rows below parent row 

DROP TABLE tabel1;
