postgresql - PostgreSQL WITH RECURSIVE order by 在非递归术语中

Question

我正在尝试创建一个递归 CTE，并且我想使用 ORDER BY 从表中获取非递归项中的行，但这似乎不可能。有什么解决方法吗？

例子：

CREATE TABLE mytable (
  id BIGSERIAL PRIMARY KEY,
  ref_id BIGINT NOT NULL,
  previous_id BIGINT REFERENCES mytable(id),
  some_name TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO mytable (id, previous_id, ref_id, some_name) VALUES (1, NULL, 1, 'Barry');
INSERT INTO mytable (id, previous_id, ref_id, some_name) VALUES (2, NULL, 1, 'Nick');
INSERT INTO mytable (id, previous_id, ref_id, some_name) VALUES (3, 1, 2, 'Janet');
INSERT INTO mytable (id, previous_id, ref_id, some_name) VALUES (4, 1, 1, 'John');
INSERT INTO mytable (id, previous_id, ref_id, some_name) VALUES (5, 2, 7, 'Ron');
INSERT INTO mytable (id, previous_id, ref_id, some_name) VALUES (6, 1, 1, 'Aaron');
INSERT INTO mytable (id, previous_id, ref_id, some_name) VALUES (7, 4, 1, 'Anna');

我正在尝试构建的查询

WITH RECURSIVE my_path AS (
  SELECT * FROM mytable
  WHERE ref_id = 1 AND some_name = 'Anna'
  ORDER BY created_at DESC
  LIMIT 1

  UNION ALL

  SELECT ph.* FROM my_path hp 
  INNER JOIN mytable ph ON hp.previous_id = ph.id
) 

SELECT * FROM my_path;

SQLFIDDLE

标签： postgresql

Answer 1

只需将其移至入门 CTE：

更新的小提琴

WITH RECURSIVE base_record as (
  SELECT * FROM mytable
  WHERE ref_id = 1 AND some_name = 'Anna'
  ORDER BY created_at DESC
  LIMIT 1

), my_path AS (
  SELECT * FROM base_record

  UNION ALL

  SELECT ph.* FROM my_path hp 
  INNER JOIN mytable ph ON hp.previous_id = ph.id
) 

SELECT * FROM my_path;