Postgresql sorting string_agg

Question

I run the following query string_agg(DISTINCT grades, '|'), which executed and outputted my result in this order 01|02|03|04|05|KG|PK.

How can I rearrange it this way PK|KG|01|02|03|04|05?

SELECT
      U.CUSTOM_100000001 AS USERID
    , SC.TITLE
    , U.FIRST_NAME
    , U.LAST_NAME
    , string_AGG(DISTINCT SGL.short_name, '|')
FROM
      USERS U
    , COURSE_PERIODS CP
    , SCHOOLS SC
    , school_gradelevels SGL
WHERE
    CP.SCHOOL_ID=SC.ID
        AND
    U.STAFF_ID = CP.TEACHER_ID
        AND
    SGL.SCHOOL_ID = SC.ID
        AND
    CP.SYEAR =2015
        AND
    SGL.short_name in('PK','KG','01','02','03','04','05','06','07','08')
        AND
    SC.CUSTOM_327 IN ('0021','0025','0051','0061','0071','0073','0081','0101','0111','0131','0211','0‌221','0294','0301','0321','0341','0361','0371','0291')
GROUP BY
    U.CUSTOM_100000001, SC.TITLE, U.FIRST_NAME, U.LAST_NAME

Can you add the rest of your query to your question? ie the entire SELECT statement — dsh
– dsh, Commented Aug 21, 2015 at 15:30
SELECT U.CUSTOM_100000001 AS USERID, SC.TITLE, U.FIRST_NAME, U.LAST_NAME, string_AGG(DISTINCT SGL.short_name, '|') FROM USERS U, COURSE_PERIODS CP, SCHOOLS SC, school_gradelevels SGL WHERE CP.SCHOOL_ID=SC.ID AND U.STAFF_ID = CP.TEACHER_ID AND SGL.SCHOOL_ID = SC.ID AND CP.SYEAR =2015 and SGL.short_name in('PK','KG','01','02','03','04','05','06','07','08') AND SC.CUSTOM_327 IN ('0021','0025','0051','0061','0071','0073','0081','0101','0111','0131','0211','0221','0294','0301','0321','0341','0361','0371','0291') GROUP BY U.CUSTOM_100000001, SC.TITLE, U.FIRST_NAME, U.LAST_NAME — user3263892
– user3263892, Commented Aug 21, 2015 at 16:32

dsh · Accepted Answer · 2015-08-21 17:12:28Z

3

It's possible in PostgreSQL 9.0+:

SELECT
    string_agg(DISTINCT SGL.short_name
            , '|' ORDER BY
                (substring(SGL.short_name, '^[0-9]+'))::int NULLS FIRST,
                 substring(SGL.short_name, '[^0-9_]+$') DESC)
FROM school_gradelevels SGL;

Test example:

WITH tbl(grade) AS (
    VALUES
     ('01'), 
     ('02'), 
     ('03'), 
     ('PK'), 
     ('KG') 
)
SELECT grade
FROM tbl
ORDER BY (substring(grade, '^[0-9]+'))::int NULLS FIRST, substring(grade, '[^0-9_]+$') DESC;

Result:

 grade
-------
 PK
 KG
 01
 02
 03
(5 rows)

Aggregate Expressions

edited Aug 21, 2015 at 17:12

dsh

12.3k3 gold badges37 silver badges53 bronze badges

answered Aug 21, 2015 at 16:40

Valery Viktorovsky

6,7863 gold badges47 silver badges52 bronze badges

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

2 Comments

user3263892 Over a year ago

GETTING THIS ERROR in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list LINE 13

Valery Viktorovsky Over a year ago

Error message is more or less self-explanatory. Can you create sqlfiddle.com for your table schema/data?

Collectives™ on Stack Overflow

Postgresql sorting string_agg

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related