PostgreSQL: Getting STRING_AGG to respect the ordering from a CTE

Question

I'm creating a report, showing staff members' former teams along with the date they left, aggregated into a single field in the report. This works to a degree:

WITH "most_recent_leave_dates" AS (
    SELECT
        staff_id, -- alphanumeric string
        team,
        MAX(date_left) AS "most_recent_date_left"
    FROM team_membership
    WHERE date_left IS NOT NULL
    GROUP BY staff_id, team
    -- I thought ordering this CTE would do it, but no
    ORDER BY staff_id, most_recent_date_left DESC
)
SELECT
    staff_id,
    STRING_AGG(
        DISTINCT CONCAT(team, ' until ' || most_recent_date_left),
        CHR(10) -- separate by newline
    ) AS "teams"
FROM most_recent_leave_dates
GROUP BY staff_id

https://www.db-fiddle.com/f/jZCcKRWNV8vLJUFsa6kq7/2

But STRING_AGG is sorting the terms alphabetically. I want them sorted by most_recent_date_left. How can I do that?

The documentation states:

Alternatively, supplying the input values from a sorted subquery will usually work.

Do I have to rewrite the CTE as a subquery…?

Answer 1

Just remove distinct from outer query as below (I don't think you need this). Your data is already sorted in cte by staffid and most_recent_date_left.

Schema (PostgreSQL v9.6)

CREATE TABLE "team_membership" (
  staff_id text,
  team text,
  date_left date
);


INSERT INTO team_membership VALUES
('aaaa', 'B team', '2019-01-01'),
('aaaa', 'A team', '2021-01-01'),
('aaaa', 'C team', '2020-01-01'),
('aaaa', 'A team', '2018-01-01');

---

Query #1

WITH "most_recent_leave_dates" AS (
    SELECT
        staff_id, 
        team,
        MAX(date_left) AS "most_recent_date_left"
    FROM team_membership
    WHERE date_left IS NOT NULL
    GROUP BY staff_id, team              
)
SELECT
    staff_id,
    STRING_AGG(
         CONCAT(team, ' until ' || most_recent_date_left),
        CHR(10) order by most_recent_date_left  desc
    ) AS "teams"
FROM most_recent_leave_dates
GROUP BY staff_id;

staff_id	teams
aaaa	A team until 2021-01-01 C team until 2020-01-01 B team until 2019-01-01

---

View on DB Fiddle

Answer 2

The ORDER BY is meaningless in the CTE. You can ORDER BY as part of the STRING_AGG():

SELECT staff_id,
       STRING_AGG(CONCAT(team, ' until ' || most_recent_date_left),
                  CHR(10)
                  ORDER BY most_recent_date_left DESC
                 ) AS "teams"
FROM most_recent_leave_dates
GROUP BY staff_id;