PostgreSQL WITH RECURSIVE order by in non recursive term

Question

I am trying to create a recursive CTE and I wanted to fetch the row in the non recursive term from the table using ORDER BY but it seems impossible to do. Is there any workaround on this?

Example:

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');

The query I am trying to construct

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

Mike Organek · Accepted Answer · 2020-07-16 18:03:02Z

2

Just move it into a starter CTE:

updated fiddle

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;

answered Jul 16, 2020 at 18:03

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

g7s Over a year ago

Nice! Didn't think of that.. Thanks

Collectives™ on Stack Overflow

PostgreSQL WITH RECURSIVE order by in non recursive term

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related