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

Gordon Linoff · Accepted Answer · 2021-03-09 17:41:20Z

4

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 9, 2021 at 17:41

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

5 Comments

Jack Deeth Over a year ago

I swear I tried that and it insisted I ORDER BY the entire CONCAT statement. Thanks, this is better.

Gordon Linoff Over a year ago

@JackDeeth . . . The ORDER BY wouldn't work with the DISTINCT, so it probably generated an error. Cheers.

Jack Deeth Over a year ago

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 Over a year ago

"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: db-fiddle.com/f/jZCcKRWNV8vLJUFsa6kq7/3

Gordon Linoff Over a year ago

@JackDeeth . . . That it sometimes works is not a guarantee. I would suggest you carefully read the accepted answer on this question: dba.stackexchange.com/questions/130552/….

Kazi Mohammad Ali Nur Romel · Accepted Answer · 2021-03-10 16:41:40Z

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

edited Mar 10, 2021 at 16:41

answered Mar 9, 2021 at 18:04

Kazi Mohammad Ali Nur Romel

16.2k2 gold badges17 silver badges29 bronze badges

2 Comments

Jack Deeth Over a year ago

As per the other answer, ordering of a CTE is not guaranteed… so ORDER BY belongs in the STRING_AGG.

Kazi Mohammad Ali Nur Romel Over a year ago

Appreciate your concern. I have also changed my answer. Best wishes.

Collectives™ on Stack Overflow

PostgreSQL: Getting STRING_AGG to respect the ordering from a CTE

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related