test.sql

-- test.sql
-- watch "sqlite3 < test.sql"

.mode table

CREATE TABLE t
 (name TEXT)
;


WITH
    fn(name) AS (VALUES
        ("Bob"),
        ("John"),
        ("Sam"),
        ("Arthur"),
        ("Ford"),
        ("Korwin"),
        ("Sophie"),
        ("Jennifer")),
    sn(surname) AS (VALUES
        ("Miller"),
        ("Raegan"),
        ("Prefect"),
        ("Dent"),
        ("Barbara"),
        ("Ambrosie"))
INSERT INTO t(name)
    SELECT name || " " || surname FROM
    fn CROSS JOIN sn
;


-- SELECT * from t;

CREATE TABLE tOrder (
  tId INTEGER,
  rnd,
  ord INTEGER,
  PRIMARY KEY (tId),
  FOREIGN KEY (tId) REFERENCES t(rowid)
);

CREATE UNIQUE INDEX tOrderIndex ON tOrder(ord)
;


WITH
    randomIds AS (SELECT t.rowid AS tId, random() AS rnd FROM t)
INSERT INTO tOrder(tId, rnd, ord)
  SELECT tId, rnd, ROW_NUMBER() OVER(ORDER BY rnd) AS ord
  FROM randomIds
;

SELECT name, ord, rnd
FROM tOrder INNER JOIN t
ON t.rowid = tOrder.tId
ORDER BY tOrder.ord;