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…?

score 3 · Accepted Answer · answered Mar 09 '21 at 17:41

3

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;

answered Mar 09 '21 at 17:41

Gordon Linoff

1,242,037
58
646
786

I swear I tried that and it insisted I `ORDER BY` the entire `CONCAT` statement. Thanks, this is better. – Jack Deeth Mar 09 '21 at 19:36
1

@JackDeeth . . . The `ORDER BY` wouldn't work with the `DISTINCT`, so it probably generated an error. Cheers. – Gordon Linoff Mar 09 '21 at 20:10
1

Maybe the question title should be "how do I write SQL and stay sane when my only debugging feedback is a tiny popup window emanating from the bowels of SAP BusinessObjects"… might have to build an offline mockup with representative dummy data. – Jack Deeth Mar 09 '21 at 23:00
"The `ORDER BY` is meaningless in the CTE" only if the CTE's ordering is overwritten by another `ORDER BY` in `STRING_AGG`, right? Without `DISTINCT` in the `STRING_AGG` I can order the results just fine by ordering the CTE only: https://www.db-fiddle.com/f/jZCcKRWNV8vLJUFsa6kq7/3 – Jack Deeth Mar 10 '21 at 14:21
1

@JackDeeth . . . That it *sometimes* works is not a guarantee. I would suggest you carefully read the accepted answer on this question: https://dba.stackexchange.com/questions/130552/in-postgresql-is-the-row-ordering-preserved-in-functions-and-ctes. – Gordon Linoff Mar 10 '21 at 14:33

Kazi Mohammad Ali Nur Romel · Answer 2 · 2021-03-10T16:41:40.197

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

As per the other answer, ordering of a CTE is not guaranteed… so `ORDER BY` belongs in the `STRING_AGG`. — Jack Deeth, Mar 10 '21 at 16:32
Appreciate your concern. I have also changed my answer. Best wishes. — Kazi Mohammad Ali Nur Romel, Mar 10 '21 at 16:42

PostgreSQL: Getting STRING_AGG to respect the ordering from a CTE

2 Answers2